0

I'm using Postgres 9.3. given this table:

CREATE TABLE release_country
(
  release integer NOT NULL,
  country integer NOT NULL,
  date_year smallint,
  date_month smallint,
  date_day smallint,
)

I want a list of the earliest record for each release, in other words there can be multiple records in the table for the same release but different country. I want a list containing the release and the earliest date, but his will not work

select distinct  release, min(t1.date_year), min(t1.date_month), min(t1.date_day)
FROM release_country t1 
GROUP BY release;

Because it considers each portion of the date seperately. How do I consider the three portions as a single date, also having to contend with only the year portion being mandatory, the month and day portions may be null.

Paul Taylor
  • 13,411
  • 42
  • 184
  • 351

2 Answers2

1
SELECT DISTINCT ON (release) *
FROM   release_country
ORDER  BY release, date_year, date_month, date_day;

I would consider storing a single date column instead of three smallint numbers. That's a lot cleaner and probably cheaper overall.

Explanation for DISTINCT ON:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thankyou Ive never of the DISTINCT ON syntax before now. So I'm right in thinking it means having ordered the results take the first record for a particular releaseid and discard the others. – Paul Taylor Aug 11 '14 at 11:53
  • The main reason we are have the date field as three separate fields is because that is how they are entered by the user. – Paul Taylor Aug 11 '14 at 11:54
  • @PaulTaylor: Yes, you got the gist of DISTINCT ON. User input: I would convert it to `date` immediately (also avoid illegal values). Look into `SELECT to_date(concat_ws('-', $year, $month, $day), 'YYYY-MM-DD')`, adapt to your actual input ... More: http://stackoverflow.com/questions/22456939/store-date-with-optional-month-day/22459892#22459892 – Erwin Brandstetter Aug 11 '14 at 12:04
0

Try something like:

SELECT distinct on (release) *
FROM release_country
ORDER BY release, date_year, date_month, date_day
Ihor Romanchenko
  • 26,995
  • 8
  • 48
  • 44