1

I have query where I use distinct and order by. Two columns used in order by clause are used in Select statement but they are concatenated. Once I run the query error message appeared: 

Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Here is example of my query:

SELECT DISTINCT
   si_last +' '+si_first AS UserName
FROM Users
ORDER BY si_last,si_first

One solution that I found is to have si_last and si_first selected one more time but separate. Something like this:

SELECT DISTINCT
   si_last +' '+si_first AS UserName,
   si_last,
   si_first
FROM Users
ORDER BY si_last,si_first

Solution above seems very inefficient and I'm wondering if there is other way to work around this problem. Please if you have any suggestions please let me know. Thank you.

espresso_coffee
  • 5,980
  • 11
  • 83
  • 193
  • Possible duplicate of [ORDER BY items must appear in the select list if SELECT DISTINCT is specified](https://stackoverflow.com/questions/265628/order-by-items-must-appear-in-the-select-list-if-select-distinct-is-specified) also see [Reason for ORDER BY items must appear in the select list if select distinct](https://stackoverflow.com/questions/18584520/reason-for-order-by-items-must-appear-in-the-select-list-if-select-distinct-is) – hatchet - done with SOverflow Jul 27 '17 at 19:36

1 Answers1

0

Just use the full expression:

SELECT DISTINCT (si_last + ' ' + si_first) AS UserName
FROM Users
ORDER BY UserName;

Alternatively, use GROUP BY:

SELECT (si_last + ' ' + si_first) AS UserName
FROM Users
GROUP BY si_last, si_first
ORDER BY si_last, si_first;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    The first of these two could result in slightly different ordering than if last and first are ordered separately. For example, in the case of dual surnames separated by a space (typical for Hispanic last names). – hatchet - done with SOverflow Jul 27 '17 at 19:15