group by

  • Thread starter Thread starter Nancy
  • Start date Start date
N

Nancy

How do I use group by for the following:
data has 3 records:
jo 8/2003 48004
jo 9/2003 48003
jo 7/2003 48009

I want the middle record to be my final answer, but if I
do a group by name, max on date, max on zip code, my final
answer will be "Jo 9/2003 48009" instead of "Jo 9/2003
48003"

Please help!

Thanks
 
You might try a query whose SQL looks something this:

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
[Your Table].[Date] =
(SELECT
Max([Self].[Date])
FROM
[Your Table] AS [Self]
WHERE
[Self].[Name] = [Your Table].[Name])
 
Thanks,

This is great ... my first time using this site.

But the techniques are in French ... how do I get it in
English !
 
This is great ... I tried it and it works ... Thanks

-----Original Message-----
You might try a query whose SQL looks something this:

SELECT
[Your Table].*
FROM
[Your Table]
WHERE
[Your Table].[Date] =
(SELECT
Max([Self].[Date])
FROM
[Your Table] AS [Self]
WHERE
[Self].[Name] = [Your Table].[Name])



Nancy said:
How do I use group by for the following:
data has 3 records:
jo 8/2003 48004
jo 9/2003 48003
jo 7/2003 48009

I want the middle record to be my final answer, but if I
do a group by name, max on date, max on zip code, my final
answer will be "Jo 9/2003 48009" instead of "Jo 9/2003
48003"

Please help!

Thanks
Click to expand...


.
Click to expand...
 
Back
Top