0

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')
;

I'm struggling use Common Table Expressions to obtain both Min values & associated date, Max values and associated date, AND the actual min/max dates from the whole dataset in the same query

;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;

Edit 1: Expected output per user

|   ID | COUNT |     MAXTEMP | MAXTEMPDATE |       MAXDATE |       MINTEMP | MINTEMPDATE |    MINDATE|
|------|-------|-------------|-------------|---------------|---------------|-------------|-----------|
| 2034 |     6 |       10.03 |  1979-03-21 |    1991-05-31 |          2.24 |  1991-05-31 |1979-03-21 |
| 8305 |     4 | 18.06320208 |  2007-02-01 |    2010-11-15 |   16.06320208 |  2002-03-11 |2002-03-11 |
5
  • 1
    So, what would the expected output look like?
    – mustaccio
    Jan 11, 2022 at 19:59
  • Sorry, edited per your questions. Jan 11, 2022 at 20:12
  • 3
    You might have the answer to this question on your first question, check the comments. If that solves your problem, I think you can delete this one to avoid duplicates. =)
    – Ronaldo
    Jan 11, 2022 at 23:00
  • Thanks! I thought I was supposed to create a new question if I was adding questions to the original Jan 12, 2022 at 18:02
  • Usually it's a good practice, but in your case the change required to solve your follow up question isn't exactly a new request (if you consider Martin Smith's answer). Oh, and don't forget to mark the answer of the other question with the green tick if it proves to be your solution ;)
    – Ronaldo
    Jan 13, 2022 at 16:13

0

Your Answer

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

Browse other questions tagged or ask your own question.