SQL: How to select rows for two conditions?

Hey I have frame which looks like this

---------+--------------------+-------------+-----+-------+--------+----------+
|IATA_CODE|             AIRPORT|         CITY|STATE|COUNTRY|LATITUDE| LONGITUDE|
+---------+--------------------+-------------+-----+-------+--------+----------+
|      ABE|Lehigh Valley Int...|    Allentown|   PA|    USA|40.65236| -75.44040|
|      ABI|Abilene Regional ...|      Abilene|   TX|    USA|32.41132| -99.68190|
|      ABQ|Albuquerque Inter...|  Albuquerque|   NM|    USA|35.04022|-106.60919|
|      ABR|Aberdeen Regional...|     Aberdeen|   SD|    USA|45.44906| -98.42183|
|      ABY|Southwest Georgia...|       Albany|   GA|    USA|31.53552| -84.19447|
|      ACK|Nantucket Memoria...|    Nantucket|   MA|    USA|41.25305| -70.06018|
|      ACT|Waco Regional Air...|         Waco|   TX|    USA|31.61129| -97.23052|
|      ACV|      Arcata Airport|Arcata/Eureka|   CA|    USA|40.97812|-124.10862|
|      ACY|Atlantic City Int...|Atlantic City|   NJ|    USA|39.45758| -74.57717|
|      ADK|        Adak Airport|         Adak|   AK|    USA|51.87796|-176.64603|
|      ADQ|      Kodiak Airport|       Kodiak|   AK|    USA|57.74997|-152.49386|
|      AEX|Alexandria Intern...|   Alexandria|   LA|    USA|31.32737| -92.54856|
|      AGS|Augusta Regional ...|      Augusta|   GA|    USA|33.36996| -81.96450|

I want to get one airport from every state which is closest to the south.

Answer

I am not sure what you mean by “closest to the south”. I will interpret it as the minimum latitude.

If so, you can use row_number():

select t.*
from (select t.*,
             row_number() over (partition by state order by latitude asc) as seqnum
      from t
     ) t
where t.seqnum = 1;

Advertisement

Answer