Ask a Question
Ask Question Login
Corporate Training
  1. Community
  2. SQL Server
  3. Question
SQL Server

How to retrieve the SQL max date in a table?

Asked by Darsh K Mar 16, 2023 940 views 1 answer
Share

About this question

 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 |

Your answer

1 Answer

More SQL Server discussions

Learn & Explore

Free tutorials and interview questions from industry experts — learn the skill, then get ready to prove it.

Latest SQL Server Blogs

Guides, tips and career advice on SQL Server from JanBask experts.