Gcq1 Gcq1 - 1 month ago 8
SQL Question

How do I get only the latitude and longitude for the 'smallest' zip

I have a table that has the columns:


  • zip

  • city

  • state

  • timezone

  • latitude

  • longitude

  • dst



For the procedure I'm working on I need to return the
city
,
state
,
latitude
and
longitude
based on the
min(zip)
grouped by
city
. So for the following rows in the table, I'd need the procedure to return the row that has the zip 18101. This would have to be done for every city in that table.

zip city state timezone latitude longitude dst
18106 Allentown PA -5 40.56929 -75.58893 1
18195 Allentown PA -5 40.57983 -75.57989 1
18175 Allentown PA -5 40.58556 -75.62106 1
18103 Allentown PA -5 40.5951 -75.45915 1
18104 Allentown PA -5 40.602 -75.52104 1
18101 Allentown PA -5 40.60285 -75.47022 1
18102 Allentown PA -5 40.60775 -75.4793 1
18105 Allentown PA -5 40.69337 -75.47115 1


Is there another way (more efficient) other than finding the
min(zip)
first grouped by
city
then using that
zip
in a separate select to get the rest?

Answer

One method uses row_number():

select z.*
from (select z.*,
             row_number() over (partition by city, state order by zip asc) as seqnum
      from zips z
     ) z
where seqnum = 1;