Select distinct and 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

General SQL Server Forums

New to SQL Server Programming

Select distinct and Order By

Author

Topic

rsvore
Starting Member

15 Posts

Posted - 2012-03-29 : 14:25:39

I need this statement to use Select Distinct but when I add my order by SQL manager says "Order By must appear in the select list if Select Distinct is specified". I'm not sure which fields to add to the Select to get it to work. I tried ttl_data.end_dt and ttl_data.est_fy but no luck. Any help to point me in the right direction. Thanks so much.

SELECT Distinct tp.surname_nm, tp.given_nm, tp.scn, tp.per_cat_cd, tp.per_grade, tp.grade, tp.prog_orig, tp.trk_link, ttl_data.end_dt,ttl_data.est_fy, tp.unit_a_dt, tp.class,
CASE ttl_data.P_cc
When ttl_data.cc then ttl_data.cc + '-' + ttl_data.ia + '-' + ttl_data.case_id +'-' + ttl_data.case_line + '-' + ttl_data.wcn
ELSE ttl_data.cc + '-' + ttl_data.ia + '-' + ttl_data.case_id + ttl_data.case_line +' -'+ ttl_data.wcn + '(' + ttl_data.p_cc +')' END as trackno,ttl_data.case_id_ds + ttl_data.case_mstr + ttl_data.trk_seq AS track
From tp
Inner JOIN ttl_data on LEFT(tp.trk_link,20) = ttl_data.case_id_ds + ttl_data.case_mstr + ttl_data.trk_seq
WHERE tp.action_cd <> 'D' AND tp.scn = @scn
ORDER BY CASE
WHEN end_dt IS NULL THEN est_fy
ELSE CONVERT(char(4),YEAR(end_dt))
END + LEFT(tp.trk_link, 20) DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-29 : 14:49:23

you can probably explain us why you need distinct in first place with some sample data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rsvore
Starting Member

15 Posts

Posted - 2012-03-29 : 14:56:43

Well I'm basing the distinct off of the trackno column that is created. For example I get several trackno for a single user which creates a new row in the results which is right but some rows repeat with the same trackno and no other data is different in the row.

rsvore
Starting Member

15 Posts

Posted - 2012-03-29 : 14:58:28

So when I add distinct it cleans up the duplicate records.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-29 : 15:03:07

as suggested please post some sample data and explain 'duplicates'

Not much use in explaining in words as we cant see data and dunno what according to you represents duplicate
Also whats the purpose of order by with case?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rsvore
Starting Member

15 Posts

Posted - 2012-03-29 : 16:06:13

Thanks for replying. I got it figured out. I moved the Case
statement that was in the order by and gave it an alias yeartrack just before the from statement, then added in the order by yeartrack.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources