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

4 votes

2 answers

36419 views

                          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 |




                        

Asked by Mr. Geologist (55 rep)

Jan 7, 2022, 09:47 PM
Last activity: Jan 11, 2022, 10:27 PM

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

Related Questions