Skip to content

Instantly share code, notes, and snippets.

@marks

marks/gist:10654576

Last active August 29, 2015 13:59
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save marks/10654576 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save marks/10654576 to your computer and use it in GitHub Desktop.
Download ZIP
SQL question
Raw
gistfile1.txt
GOAL: return only the rows with the latest dates, for each param
EXAMPLE: return ids 6243, 6242, 8814, 8813, 8815
date | id | site_id | param
----------------------------------------
2014-04-14 6243 180890022 OZONE-8HR
2014-04-14 6242 180890022 OZONE-1HR
2014-04-12 8811 180890022 OZONE-8HR
2014-04-12 8814 180890022 PM2.5-24hr
2014-04-12 8813 180890022 PM10-24hr
2014-04-12 8815 180890022 SO2-24HR
2014-04-12 8812 180890022 OZONE-1HR
Raw
gistfile2.txt
SELECT
T .*
FROM
epa_data T
INNER JOIN (
SELECT
epa_data."parameter",
MAX (DATE) AS MaxDate
FROM
epa_data
GROUP BY
"parameter"
) tm ON T ."parameter" = tm."parameter"
AND T . DATE = tm.MaxDate
AND T .aqs_id = '180890022'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment