How to retrieve the maximum value and its corresponding date in a table

Question

I'm trying to figure 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 |

Answer 1

A potentially more efficient but more obscure way is to just extract the desired value using your original group by

SELECT ID,
       COUNT = COUNT(TEMP),
       MAXTEMP = MAX(TEMP),
       MAXTEMPDATE = CAST(RIGHT(MAX(FORMAT(CAST([TEMP] + 500 AS DECIMAL(15, 10)), '00000.0000000000')
                                    + FORMAT([DATE], 'yyyy-MM-dd')), 10) AS DATE),
       MAXDATE = MAX(DATE)
FROM   mytable
GROUP  BY ID; 

Because temperatures can be negative this first increments them all by 500 so I don't have to worry about negatives (absolute zero is −273.15 °C/−459.67 °F). The adjusted value is never used except by the MAX so there is no need to invert this anywhere.

It then converts that result to a 16 character string with the first 5 characters reserved for the integer component and final 10 for the decimal (both sides padded with zeros as necessary). This means that the lexicographical sorting of the value will match the numeric sorting.

Finally Date is tacked onto the end of it - the MAX is found and the Date retrieved from its known location in the concatenated string.

If you have an index on mytable(ID) INCLUDE (TEMP, DATE) the execution plan for this is pretty efficient.

---

As for why negative values would present a potential problem here it is because we must ensure that the string representation is ordered in exactly the same way as the numeric representation would be. My first thought was to have a leading character denoting sign so negative values were sorted first. The next problem is -12 is higher than -20 but the string representation 00020 would sort higher than 00012. Applying the sensible offset for the domain of values a temperature would hold allows me to avoid all that. Also apply a CHECK constraint to ensure that nonsensical temperature values can't exist in the data.

Answer 2

You can achieve it using a common table expression (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;