Select DISTINCT with ORDER BY - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Select DISTINCT with ORDER BY

Author

Topic

san
Starting Member

26 Posts

Posted - 2002-06-06 : 19:46:31

Can any one tell what is the problem with this query :

I am getting the message
"ORDER BY items must appear in the select list if SELECT DISTINCT is specified."

Here is my Query (simplified). I had given everything in the ORDER BY caluase in select. Still it is giving me the error. Please help.

=================
Select distinct
c.objid,
c.id_number,
firstprogramcode,
gbstseverity.rank,
c.requested_eta,

case
when c.requested_eta_cst = "1753-01-01 00:00:000" then null
when c.requested_eta_cst is null then null
else
c.requested_eta_cst
end as new_requested_eta,

from
table_qml_caseall as c ,
table_qml_gbst_elm as gbstseverity with (nolock)

ORDER BY
CASE
When FIRSTPROGRAMCODE is null then "zzzzzzzz"
When Ltrim(FIRSTPROGRAMCODE) =":" then "zzzzzzzz"
When FIRSTPROGRAMCODE = "" then "zzzzzzzz" Else Left(LTRIM(FIRSTPROGRAMCODE),2)
End ,
gbstseverity.rank ,
case
when c.requested_eta is null then dateadd(yy,20,getdate())
when c.requested_eta = "1753-01-01 00:00:000" then dateadd(yy,20,getdate())
else c.requested_eta

end
===================================

Edited by - san on 06/06/2002 19:48:50

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-06 : 20:04:17

quote:
I had given everything in the ORDER BY caluase in select.

Um, no you didn't:

ORDER BY CASE
When FIRSTPROGRAMCODE is null then "zzzzzzzz"
When Ltrim(FIRSTPROGRAMCODE) =":" then "zzzzzzzz"
When FIRSTPROGRAMCODE = "" then "zzzzzzzz"
Else Left(LTRIM(FIRSTPROGRAMCODE),2) End

This does not appear in the SELECT list you posted. DISTINCT needs to perform some sorting in order to work correctly, therefore the SELECT list and the ORDER BY list must match if an ORDER BY clause is used.

san
Starting Member

26 Posts

Posted - 2002-06-07 : 10:04:46

Hi rob,
Thanks fro responding to it. I am not a guru in SQL. So If you could find some time, would you please reply to this post with a
'will work' query. I really need help. I donot know how to put all 'when' and 'case' in select.

San.

quote:

quote:
I had given everything in the ORDER BY caluase in select.

Um, no you didn't:

ORDER BY CASE
When FIRSTPROGRAMCODE is null then "zzzzzzzz"
When Ltrim(FIRSTPROGRAMCODE) =":" then "zzzzzzzz"
When FIRSTPROGRAMCODE = "" then "zzzzzzzz"
Else Left(LTRIM(FIRSTPROGRAMCODE),2) End

This does not appear in the SELECT list you posted. DISTINCT needs to perform some sorting in order to work correctly, therefore the SELECT list and the ORDER BY list must match if an ORDER BY clause is used.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-07 : 10:40:24

quote:
Here is my Query (simplified)

For future reference, DO NOT post part of your query, or modify it. POST THE ACTUAL, ORIGINAL, UNEDITED, COMPLETE query. It is really difficult to troubleshoot something if it's not complete. Worse, to come up with a solution that DOESN'T work because it would conflict with part of the query that was left out.

Second, PLEASE POST your full table structures, list all columns and their datatypes. Also post some sample data, and some samples of the EXACT output you want to get from the query. Also explain what you want the query to do, what you don't want it to do, etc. Give as much detail as possible.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources