SQL: Take the oldest date from a table like [ date_year, date_month, date_day ]

Question

i have this table called RELEASE:

*Country, Album, Date_year, Date_month, Date_day*
  Italy    Z      1940       2           27
  Italy    Y      1992       11          22
  Italy    X      1940       1           20
  Italy    R      1998       null        null
  France   W      1944       9           18
  UK       L      1989       8           21
  UK       P      1970       10          1
  Germany  E      2002       null        null

I need to specify a SQL query that take the name of album, the name of country and the date (year, month, day) of the oldest album.

(it's ok also if the values of month and day are null)

I can't use LIMIT, OFFSET, ROWNUM... i can use only standard SQL constructs.

I try to make this query but it isn't correct:

SELECT country, album, min(date_year), min(date_month), min(date_day)
FROM release

The result it would be:

 *Country, Album, Date_year, Date_month, Date_day*
  Italy    X      1940       1           20

How i can solve? Thanks

don't you need to group by country, album (the non-aggregrated fields) order by year,month,day limit 1 — xQbert, Jul 13 '17 at 14:10
@ValentinGenevrais LIMIT has been standardized in SQL:2008. It doesn't exist in SQL Server 2000/2005 for instance, it doesn't exist in Oracle. — arvymetal, Jul 13 '17 at 14:13
Does NULL imply the start or the end of the specified period? — JeffUK, Jul 13 '17 at 15:35
@JeffUK (i think) null imply the first day for month and the first month for year — Ralph, Jul 13 '17 at 15:43

JeffUK · Answer 1 · 2017-07-13T16:34:54.503

0

Not tested this, I'd be amazed if it works. Instead of right you should probably use mid(8,len()) h (as the left is always 8 characters)

SELECT 
    release.country, 
    right(
        min(

                cast(Date_year as nvarchar)+  cast(Date_month as nvarchar) + cast(Date_Day as varchar) +album
        ),1) Album,
     min(cast(Date_year as nvarchar)+  cast(Date_month as nvarchar) + cast(Date_Day as varchar) +album) minDate
from release
group by country

edited Jul 13 '17 at 16:34

answered Jul 13 '17 at 15:42

JeffUK

4,107
2
20
34

Thank you @JeffUK for the answer but i can use only standard SQL constructs. I can't use concat for example – Ralph Jul 13 '17 at 15:45
Concat isn't standard? wow. You could use `||` I can't test that as SQL server doesn't support it. – JeffUK Jul 13 '17 at 15:49
is INSERT() Standard ANSI http://www.smallsql.de/doc/sql-functions/string/insert.html ? I don't have the standard to hand.. would it accept an int instead of the first string? – JeffUK Jul 13 '17 at 16:00
@ralph how about now? CAST is in the 1992 standard; I prefer my other answer but its good to have options. – JeffUK Jul 13 '17 at 16:36
The exercise is set to training and understand the mechanisms without using any functions – Ralph Jul 13 '17 at 18:21
Oh hang on, you can't move the goalposts, you said you must you things in the ANSI standard; CAST is is in the ANSI standard. – JeffUK Jul 13 '17 at 22:09

JeffUK · Answer 2 · 2017-07-13T16:28:12.283

This should work, ultimately all you need to do is build the date in a sortable format, then sort by it.

select release.country, Album, Date_Year, Date_Month, Date_Day from RELEASE

left join
(
        select country, 
               min(date_year*10000+date_month*100+date_day) minDay 
        from RELEASE
        group by country) albumDay

on albumDay.country = RELEASE.country

where 
date_year*10000+date_month*100+date_day = minDay

With the proviso that if you have multiple 'oldest' albums, it will show all of the joint oldest. The problem statement didn't specify how to handle this.

You need to add NULL handling (replace every reference to a date field with coalesce(date_foo,0); or coalesce(date_foo,99); depending on how you want to treat them.

SQL: Take the oldest date from a table like [ date_year, date_month, date_day ]

2 Answers2