4

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 |

2 Answers 2

6

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.

enter image description here


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.

0
9

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;
3
  • Thanks Ronald! I'm struggling with adding a second CTE query to obtain the min associated values. Jan 11, 2022 at 19:27
  • @Mr.Geologist, glad to assist. Have you considered using the method suggested by Martin Smith? It is more efficient and you could obtain the min associated values just by adding the following:
    – Ronaldo
    Jan 11, 2022 at 22:32
  • 1
    , MINTEMP = MIN(TEMP), MINTEMPDATE = CAST(RIGHT(MIN(FORMAT(CAST([TEMP] + 500 AS DECIMAL(15, 10)), '00000.0000000000') + FORMAT([DATE], 'yyyy-MM-dd')), 10) AS DATE), MINDATE = MIN(DATE)
    – Ronaldo
    Jan 11, 2022 at 22:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.