SQL Displaying a maximum #ID from the right 3 numbers in ID only for each name in a different column

Question

question

Ed1852 asked Dec 3, '14

SQL Displaying a maximum #ID from the right 3 numbers in ID only for each name in a different column

I am trying to only display the maximum values of each set of project id values using this select Project_Id, cast(RIGHT(Geo_ID,4)as int) as Geo_ID from Geography group by Project_ID, Geo_ID order by project_id, RIGHT(Geo_ID,4) desc gives me this: Project_Id Geo_ID 1 1 8 9 8 8 8 7 8 6 8 5 8 4 8 3 8 2 8 1 12 1 17 1 22 10 22 9 22 8 But I want to get this to display only: Project_Id Geo_ID 1 1 8 9 12 1 17 1 22 10 Thanks so much for your help.

sql-server-2008

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DenisT commented · Dec 03, 2014 at 03:03 PM

You need to use SELECT Project_Id, MAX(Geo_Id) then GROUP BY Project_Id

1 ·

Answer 1 · 2014-12-03T15:01:09Z

Kev Riley answered Dec 3, '14

You just need to aggregate the 2nd expression using MAX() select Project_Id, max(cast(RIGHT(Geo_ID,4)as int)) as Geo_ID from Geography group by Project_ID order by project_id

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ed1852 commented · Dec 03, 2014 at 03:05 PM

THANKS!!!!!! its really the simple things that trips me up.

0 ·

question