Need a little help with a query

[thelarch]

I have a table and it contains values like these ones below:

18/05/2006 50
18/05/2006 35
19/05/2006 50
19/05/2006 69
20/05/2006 55
20/05/2006 33

Basically i want my query to output the date and the highest number on
that date. Therefore the result being:

18/05/2006 50
19/05/2006 69
20/05/2006 55

Im not sure which was to go about this. Any help would be appreciated.
Thanks

 

[Steve Perrins]

Hi Nick,

Try the following

SELECT s_date, MAX(s_val)
FROM test1
GROUP BY s_date;

Steve Perrins,
SCP Computers
http://www.scp-computers.co.uk

 

[Domac]

Here it is!


SELECT Table1.Date, Max(Table1.Value) AS MaxOfValue
FROM Table1
GROUP BY Table1.Date;

 

[thelarch]

Thanks heaps guys! It works great!

 

[thelarch]

OK i would like to take it one step further now.

The table will contain values like these ones below:

18/05/2006 50 Chris
18/05/2006 30 Chris
18/05/2006 40 Peter
18/05/2006 35 Peter
19/05/2006 50 Chris
19/05/2006 52 Chris
19/05/2006 55 Peter
19/05/2006 33 Peter

Basically i would like it to get the highest value for each person each
day, add them together and display there name and total in the query.
There could be up to about 20 days in total. I would like the result
to look like this:

102 Chris
95 Peter

Thanks for your help

 

[John Spencer]

Two query approach:

Query One saved as qGetMax
SELECT [DateField], [NameField], Max([ValueField]) as TodaysMax
FROM TheTable
GROUP BY [DateField], [NameField]

Query Two
SELECT [NameField], Sum(TodaysMax) as TheTotal
FROM qGetMax
GROUP BY [NameField]

IF your field and table names don't have spaces or special characters (only
letters, numbers, and underscores) you can do this all in one query

SELECT NameField, Sum(TodaysMax) as TheTotal
FROM
(SELECT DateField, NameField, Max(ValueField) as TodaysMax
FROM TheTable
GROUP BY DateField, NameField) as SourceQuery
GROUP BY NameField

 

[thelarch]

Thanks heaps John! Exactly what i needed. The single query approach
worked fine.