CODEWITHSUNDEEP
sqlsql-serverdistinctcase
Haider
Haider

Reputation: 33

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

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

Upvotes: 3

Views: 23368

Answers (3)

mechanical_meat
mechanical_meat

Reputation: 169274

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

Upvotes: 12

lc.
lc.

Reputation: 116448

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

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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?

Upvotes: 0

Related Questions