Need a little help with a query

T

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
 
D

Domac

Here it is!


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

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
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top