What is the best way to display the details of the highest value-CodePudding

Hello I would like to know what is the right way, I was asked to present the information about the artist who died the oldest.

FROM ListOfArtists
WHERE (DateDeceased - DateofBirth) = (SELECT 
            MAX(DateDeceased - DateofBirth)
        FROM ListOfArtists);

Or:

SELECT *
FROM ListOfArtists
ORDER BY (DateDeceased - DateofBirth) DESC
LIMIT 1

Thanks in advance!

CodePudding user response：

I apologise if I have completely over-thought this but I could not resist pointing this out.

Your first comment suggests you are storing just year of birth and death -

ArtistID	LastName	FirstName	Nationality	DateDeceased	DateOfBirth
1	Botticelli	Sandro	Italian	1510	1445
2	Da Vinci	Leonardo	Italian	1519	1452
3	Buonarroti	Michelangelo	Italian	1564	1475
4	Bonestell	Chesley	American	1986	1888
5	Chagall	Marc	French	1985	1887

Your first query -

SELECT *
FROM ListOfArtists
WHERE (DateDeceased - DateofBirth) = (SELECT 
            MAX(DateDeceased - DateofBirth)
        FROM ListOfArtists);

returns -

ArtistID	LastName	FirstName	Nationality	DateDeceased	DateOfBirth
4	Bonestell	Chesley	American	1986	1888
5	Chagall	Marc	French	1985	1887

Your second query -

SELECT *
FROM ListOfArtists
ORDER BY (DateDeceased - DateofBirth) DESC
LIMIT 1

returns -

ArtistID	LastName	FirstName	Nationality	DateDeceased	DateOfBirth
5	Chagall	Marc	French	1985	1887

Now consider the following version of your table but storing complete dates for birth and death -

ArtistID	LastName	FirstName	Nationality	DateDeceased	DateOfBirth
1	Botticelli	Sandro	Italian	1510-05-17	1445-01-01
2	Da Vinci	Leonardo	Italian	1519-05-02	1452-04-15
3	Buonarroti	Michelangelo	Italian	1564-02-18	1475-03-06
4	Bonestell	Chesley	American	1986-06-11	1888-01-01
5	Chagall	Marc	French	1985-03-28	1887-07-07

Your first query returns -

ArtistID	LastName	FirstName	Nationality	DateDeceased	DateOfBirth
4	Bonestell	Chesley	American	1986-06-11	1888-01-01

Your second query returns -

ArtistID	LastName	FirstName	Nationality	DateDeceased	DateOfBirth
4	Bonestell	Chesley	American	1986-06-11	1888-01-01

A better query would be -

SELECT *
FROM ListOfArtists
ORDER BY DATEDIFF(DateDeceased, DateofBirth) DESC
LIMIT 1;

which returns -

ArtistID	LastName	FirstName	Nationality	DateDeceased	DateOfBirth
4	Bonestell	Chesley	American	1986-06-11	1888-01-01

The following query illustrates the difference between the two -

SELECT *, YEAR(DateDeceased) YearDied, YEAR(DateofBirth) YearBorn, (DateDeceased - DateofBirth), DATEDIFF(DateDeceased, DateofBirth) DaysOld, FLOOR(DATEDIFF(DateDeceased, DateofBirth) / 365.25) YearsOld
FROM ListOfArtists;

ArtistID	LastName	FirstName	Nationality	DateDeceased	DateOfBirth	YearDied	YearBorn	(DateDeceased - DateofBirth)	DaysOld	YearsOld
1	Botticelli	Sandro	Italian	1510-05-17	1445-01-01	1510	1445	650416	23876	65
2	Da Vinci	Leonardo	Italian	1519-05-02	1452-04-15	1519	1452	670087	24487	67
3	Buonarroti	Michelangelo	Italian	1564-02-18	1475-03-06	1564	1475	889912	32490	88
4	Bonestell	Chesley	American	1986-06-11	1888-01-01	1986	1888	980510	35955	98
5	Chagall	Marc	French	1985-03-28	1887-07-07	1985	1887	979621	35693	97

Here's a SQL Fiddle