Query Maximum value per row

  • Thread starter Thread starter Wil
  • Start date Start date
W

Wil

Hi,

I have a table with date, hour, and traffic data. The table was updated in
an hourly basis,e.g.

date hour traffic
5/1/05 0 100
5/1/05 1 102
...
...
5/2/05 22 130
5/2/05 23 200

My question is, how will I select the maximum traffic value per date?
Sample output:

date hour traffic
5/1/05 1 102
5/2/05 23 200


Regards,
Wilfredo
 
Create your query with date as the first column and traffic as the second.
Click on the group by button on the toolbar and leave group by for the date
but change the group by on the traffic to Max.
 
SELECT [Date], [Hour], Traffic
FROM YourTable
WHERE Traffic =
(SELECT Max(T.Traffic)
FROM YourTable As T
WHERE T.[Date] = YourTable.[Date])

If the biggest traffic is the same for two different hours on the same date you
will get two records back.

Another method is to use two queries.

Query One
SELECT Max(T.Traffic) as BigOne, [Date]
FROM YourTable As T
GROUP BY [Date]

Then having saved that query, use it in a second query.
SELECT [Date], [Hour], Traffic
FROM YourTable Inner Join QueryOne
 
Back
Top