SQL Aggregate Key and getting Primary key

Question

i am looking to get the primary key from a group by.

What i am looking for is to select all the Maximum and Minimum temperature for a day and get the primary key for that entry.

select Cast(DateTime as date) AS 'Date', MAX(Temperature) AS 'Max Temperature'
from [Testing].[dbo].[Reading]
GROUP by Cast(DateTime as date)
ORDER by Cast(DateTime as date)

This will find me the maximum temperature for each day however due to the group by i am unable to get the primary key for that entry that is the highest for that day. Any Help?

What if there are more readings with the same temperature? Dublicate? [Fetch the row which has the Max value for a column](http://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column) — Edwin Stoteler, Nov 25 '15 at 15:13
For this case all i care about it the maximum temperature on a day, if that happened once or twice does not matter — RobouteGuiliman, Nov 25 '15 at 15:16
The first occurrence, this will be used to display temperature by day so it does not matter the actual time the reading happened. — RobouteGuiliman, Nov 25 '15 at 15:20
Then why do you need the key? You already have the temperature and day. — Edwin Stoteler, Nov 25 '15 at 15:29
What is the Primary Key of the table? Do you seriously have a column named "DateTime"? — Tab Alleman, Nov 25 '15 at 15:33
I need the primary key so that i can then obtain the foreign key in the same row. Since i will have many similar readings from different foreign keys Primary key is ReadingId, and yes i do have a column named DateTime, i am planning to changing it i am just going through some very early development — RobouteGuiliman, Nov 25 '15 at 15:34

Gordon Linoff · Answer 1 · 2015-11-25T18:18:12.723

One method is to use window functions and conditional aggregation:

select Cast(DateTime as date) AS [Date], MAX(Temperature) AS MaxTemperature,
       max(case when seqnum = 1 then temperature end) as LastTemperature
from (select r.*,
             row_number() over (partition by Cast(DateTime as date)
                                order by DateTime desc) as seqnum
      from [Testing].[dbo].[Reading] r
     ) r
group by Cast(DateTime as date)
order by Cast(DateTime as date);

EDIT:

If you want other values from the row with the maximum temperature, then you would use:

select Cast(DateTime as date) AS [Date], MAX(Temperature) AS MaxTemperature,
       max(case when seqnum_max = 1 then <whatever column here> end) as <whatever column>
from (select r.*,
             row_number() over (partition by Cast(DateTime as date)
                                order by Temperature desc) as seqnum_max
      from [Testing].[dbo].[Reading] r
     ) r
group by Cast(DateTime as date)
order by Cast(DateTime as date);

You would use another variable for the minimum.

This does not give me the primary key from which the maximum temperature comes from — RobouteGuiliman, Nov 25 '15 at 15:25
Its good, just change ```MAX(Temperature) AS MaxTemperature``` by ```Temperature AS MaxTemperature``` and change ```order by DateTime desc``` in the partition by ```order by DateTime desc, Temperature desc``` and remove the group by — Tiago Oliveira de Freitas, Nov 25 '15 at 15:46

score 0 · Accepted Answer · answered Nov 25 '15 at 15:47

Based on @Gordon answer:

select Cast(DateTime as date) AS [Date], Temperature AS MaxTemperature,     id_temperature
from (select r.*,
             row_number() over (partition by Cast(DateTime as date)
                                order by DateTime desc, Temperature desc) as seqnum
      from [Testing].[dbo].[Reading] r
     ) r
order by Cast(DateTime as date);

SQL Aggregate Key and getting Primary key

2 Answers2

Linked