How to retrieve the SQL max date in a table?
I'm trying to figure out how to retrieve minimum/maximum values and minimum/maximum dates from a data set, but also the date value that corresponds to each minimum/maximum value.
Example Data
CREATE TABLE mytable
([ID] int, [TEMP] FLOAT, [DATE] DATE)
;
INSERT INTO mytable
([ID], [TEMP], [DATE])
VALUES
(8305, 16.38320208, '03/22/2002'),
(8305, 17.78320208, '11/15/2010'),
(8305, 16.06320208, '03/11/2002'),
(8305, 18.06320208, '02/01/2007'),
(2034, 5.2, '03/12/1985'),
(2034, 2.24, '05/31/1991'),
(2034, 6.91, '09/15/1981'),
(2034, 7.98, '07/16/1980'),
(2034, 10.03, '03/21/1979'),
(2034, 6.85, '11/19/1982')
;
Querying for minimum/maximum of the TEMP and DATE columns:
SELECT ID,
COUNT(TEMP) AS COUNT,
MAX(TEMP) AS MAXTEMP,
MAX(DATE) AS MAXDATE
FROM mytable
GROUP BY ID;
retrieves this:
| ID | COUNT | MAXTEMP | MAXDATE |
|------|-------|-------------|------------|
| 2034 | 6 | 10.03 | 1991-05-31 |
| 8305 | 4 | 18.06320208 | 2010-11-15 |
But I would like to figure out how to retrieve this:
| ID | COUNT | MAXTEMP |MAXTEMPDATE | MAXDATE|
| 2034 | 6 | 10.03 | 1979-03-21 |1991-05-31 |
| 8305 | 4 | 18.06320208 | 2007-02-01 |2010-11-15 |
You can retrieve SQL max date in a table by using a common table [removed]CTE) and ROW_NUMBER():;WITH cte AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [TEMP] DESC) AS rn
FROM mytable
)
SELECT cte.ID,
COUNT(*) AS COUNT,
cte.TEMP AS MAXTEMP,
cte.DATE AS MAXTEMPDATE,
MAX(mt.DATE) AS MAXDATE
FROM cte
INNER JOIN mytable mt ON cte.ID = mt.ID
WHERE rn = 1
GROUP BY cte.ID,
cte.TEMP,
cte.DATE;