Query Max, Min and Group By problem

[marcin2k]

Hello,
I am trying to make a summary table. I have a table with a bunch of data that contains like over 100 records for a single day. I am making a query to display the max and min temperatures for each day. Thats easy because I group by day and then use max and min but the tricky part is, How can I display the correct ID with this max or min. Each record has an ID associated with its record.

Thanks any help would be appreciated.
Marcin

 

[PWise]

create a new query bring in your group query (i called it minmax) and bring in the temperatures table (i called it temp) as per sample below

note if you have 2 the same temperatures for the same day you are going to have to tweak this some



SELECT minmax.*, mintemp.ID AS minid, maxtemp.ID AS maxtemp
FROM (minmax INNER JOIN temp AS mintemp ON (minmax.MinOftemp = mintemp.temp) AND (minmax.day = mintemp.day)) INNER JOIN temp AS maxtemp ON (minmax.day = maxtemp.day) AND (minmax.MaxOftemp = maxtemp.temp);

 

[marcin2k]

ya thats my problem, there might be a possiblity of two records with the min or max temperature

 

[PWise]

well what id do you want when there are more then 1 of the min

 

[PHV]

Something like this (SQL code) ?
SELECT A.[day field], A.MaxTemp, X.ID As IDofMax, A.MinTemp, Y.ID As IDofMin
FROM ((SELECT [day field], Max([temperature field]) As MaxTemp, Min([temperature field]) As MinTemp
FROM yourTable GROUP BY [day field]) As A
INNER JOIN yourTable As X ON A.[day field] = X.[day field] AND A.MaxTemp = X.[temperature field])
INNER JOIN yourTable As Y ON A.[day field] = Y.[day field] AND A.MinTemp = Y.[temperature field]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[marcin2k]

Well my acutal application is a bit more complex i though getting the ID would be the same.
Here is the thing I am doing:
THe data table has contains a field with date and time, and there is another field with temperature.
I have a query which formats the date to just the date and groups by days, then it finds a max and min for each day.
Now I need to find the time that each max and min took place at. In case there is more then one record with the same min or max temp, I would pick the first time

Hope this explains it, I was hoping i can add an expression to my query which finds the max or min. Maybe use a function but still thinking about it

 

[PWise]

pvh
still gives 2 records when more then 1 of the min or max

 

[PHV]

SELECT A.Date, A.MaxTemp, Min([X.Time expression]) As TimeOfMax, A.MinTemp, Min([Y.Time expression]) As TimeOfMin
FROM ((SELECT [day expression] As [Date], Max([temperature field]) As MaxTemp, Min([temperature field]) As MinTemp
FROM yourTable GROUP BY [day expression]) As A
INNER JOIN yourTable As X ON A.Date = X.[day expression] AND A.MaxTemp = X.[temperature field])
INNER JOIN yourTable As Y ON A.Date = Y.[day expression] AND A.MinTemp = Y.[temperature field]
GROUP BY A.Date, A.MaxTemp, A.MinTemp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[marcin2k]

Thanks for the help, I went with making multiple queries and then linking them through by temperature and date. I guess I will display more then one record for day if there was a max at various times.

Marcin