ORDER BY items must appear in the select list if SELECT DISTINCT is specified..... I spents hours but nothing found

Question

Below is the part of my large sql query and I could not find a solution of my problem after spending hours on it. My requirement is only sort the records on RaceNumber ascending and put null records on bottom

SELECT DISTINCT TP.racenumber, 
                TP.teamid, 
                TP.teamname 
FROM   tblteamprofile TP 
ORDER  BY CASE 
            WHEN TP.racenumber IS NULL THEN 1 
            ELSE 0 
          end, 
          TP.teamid, 
          TP.teamname

Can anybody help me it is only my humble request...! please

possible duplicate of [ORDER BY items must appear in the select list if SELECT DISTINCT is specified error](http://stackoverflow.com/questions/15533128/order-by-items-must-appear-in-the-select-list-if-select-distinct-is-specified-er) — nalply, May 15 '13 at 09:07
another duplicate: http://stackoverflow.com/questions/265628/220060 — nalply, May 15 '13 at 09:43

mechanical_meat · Answer 1 · 2013-02-21T16:33:13.680

12

Add the missing ORDER BY item to the SELECT list:

SELECT DISTINCT   
  TP.RaceNumber, 
  CASE WHEN TP.RaceNumber is null THEN 1 ELSE 0 END, 
  TP.TeamID,     
  TP.TeamName     
FROM 
  tblTeamProfile TP 
ORDER BY 
  CASE WHEN TP.RaceNumber is null THEN 1 ELSE 0 END,
  TP.TeamID,
  TP.TeamName

As Factor Mystic astutely notes in the comments, you can simplify the query if your RDBMS allows re-using an alias in the ORDER BY clause:

SELECT DISTINCT   
  TP.RaceNumber, 
  CASE WHEN TP.RaceNumber is null THEN 1 ELSE 0 END AS RaceNumberFlag, 
  TP.TeamID,     
  TP.TeamName     
FROM 
  tblTeamProfile TP 
ORDER BY 
  RaceNumberFlag,
  TP.TeamID,
  TP.TeamName

edited Feb 21 '13 at 16:33

answered Feb 21 '13 at 16:27

mechanical_meat

163,903
24
228
223

In the cause of DRY, give the `case` an alias in the select clause and reference it by alias in the order by – Factor Mystic Feb 21 '13 at 16:28
2

Thanks for the comment. Unfortunately not all RDBMSs allow that. – mechanical_meat Feb 21 '13 at 16:28
+1 -- SQL Server requires putting all order by fields used in the select statement when distinct is used. Nice answer. – sgeddes Feb 21 '13 at 16:28
@bernie but sql server does, and the question is referring to sql server – Factor Mystic Feb 21 '13 at 16:31
A similar tactic is to push RaceNumberFlag into a sub query using CROSS APPLY. This allows you to GROUP BY that item or perform further operations on the product. This is especially applicable for more complex statements. – mrmillsy Nov 10 '14 at 12:27

score 3 · Answer 2 · answered Feb 21 '13 at 16:30

3

The problem is the database engine doesn't know how to select out that particular value if it is not part of the SELECT list in a SELECT DISTINCT query.

You should be able to use a GROUP BY instead of DISTINCT if you don't want to select the column:

SELECT TP.racenumber, TP.teamid, TP.teamname 
FROM tblteamprofile TP 
GROUP BY TP.racenumber, TP.teamid, TP.teamname, 
    CASE WHEN TP.racenumber IS NULL THEN 1 ELSE 0 END
ORDER BY CASE WHEN TP.racenumber IS NULL THEN 1 ELSE 0 END, 
    TP.teamid, TP.teamname

answered Feb 21 '13 at 16:30

lc.

113,939
20
158
187

I upvoted this one because it requires the least invasive changes to the code and doesn't change your output. My answer involved a nested subquery, but I like this more so I won't bother posting. Cheers! – Eric J. Price Feb 21 '13 at 17:32
1

Why did you include the `CASE` in the GROUP BY ? – Yann39 Mar 04 '14 at 10:04
@Yann39 You're right, if `TP.racenumber` is already in the GROUP BY it shouldn't matter. – lc. Mar 05 '14 at 07:17

score 0 · Answer 3 · answered Feb 21 '13 at 16:27

0

Perhaps the order by should be this:

order by (case when tp.RaceNumber is NULL then 1 else 0 end),
         tp.RaceNumber

If you want to order by RaceNumber, why do you have TeamId and TeamName in the query?

answered Feb 21 '13 at 16:27

Gordon Linoff

1,242,037
58
646
786

ORDER BY items must appear in the select list if SELECT DISTINCT is specified..... I spents hours but nothing found

3 Answers3