SQL Query for CONCAT and DISTINCT or GROUP BY

Question

hello guys,

i have this query:

SELECT CONCAT(RTRIM([lastname]), ', ', LTRIM([firstname])) AS name, [oc] as user_id
  FROM [table]
  WHERE [priorita_max]='100'
  ORDER BY [lastname],[firstname] ASC

• please note that ORDER BY is very important here

and I need results to be DISTINCT...
so one option is to use DISTINCT directly,
or GROUP BY [oc] / user_id

however for both cases it gives me errors...

Can you please share with what would be correct way to do that?

Answer 1

I guess you can try:

 SELECT CONCAT(RTRIM([lastname]), ', ', LTRIM([firstname])) AS name, [oc] as user_id
   FROM [table]
   WHERE [priorita_max]='100'
   ORDER BY CONCAT(RTRIM([lastname]), ', ', LTRIM([firstname])) ASC

Answer 2

Try something like this too:

;
with E as
(
    select distinct oc, firstname, lastname
    from [table]
    where [priorita_max] = '100'
)
select CONCAT(RTRIM([lastname]), ', ', LTRIM([firstname])) AS name, [oc] as user_id
from E
order by lastname, firstname

Answer 3

Basicaly I have list of persons such as:

OC      FNAME   LNAME   CARDnumber
---------------------------------------------------
1111        fn1     ln1     016516515
1111        fn1     ln1     546546541
1111        fn1     ln1     232456345
1112        fn2     ln2     524525436
1112        fn2     ln2     546453245
1112        fn2     ln2     545354224
1113        fn3     ln3     567251233
1113        fn3     ln3     152375727
1113        fn3     ln3     897541567

as you can see, it ID (OC) and name is always same, however one person can have any amount of CARDnumberts assigned to him...
however what I need to do is DISTINCT OC and CONCAT(lname,", ",fname) and cardnumber doesnt bother me...

so If i would just simply:

SELECT DISTINCT [oc], CONCAT(lname, ", ", fname)
ORDER BY lname ASC

then i have each person there three times obviously.

When I add:

GROUP BY [oc]

im having an error:
Msg 8120, Level 16, State 1, Line 1
Column 'lname' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'fname' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.