SQL Group BY Question

Forum Moderators: open

Message Too Old, No Replies

SQL Group BY Question

All Fields

bgolfer66

8:01 pm on Nov 5, 2007 (gmt 0)

I want to select records from a table that contains an ID, Date, and let's say other columns, Something like this

ID DATE FIRST NAME LAST NAME
1 10/1/07 JOHN DOE
1 10/15/07 FRED BROWN
1 11/1/07 JOE BLOW
2 8/7/07 FRED SMITH
2 9/15/07 MARY JANE
3 9/15/07 JERRY SEINFELD

MY Query should return:

ID DATE FIRST NAME LAST NAME
1 11/1/07 JOE BLOW
2 9/15/07 MARY JANE
3 9/15/07 JERRY SEINFELD

one record per ID with the latest date.

HELP! I'm losing my mind... :-)

John

phranque

1:18 am on Nov 6, 2007 (gmt 0)

welcome to WebmasterWorld, bgolfer66!

try this:
select ID, MAX(DATE), FIRST NAME, LAST NAME from TABLE_NAME group by ID;

bgolfer66

6:46 pm on Nov 6, 2007 (gmt 0)

That works for me in SQL Server, but alas, this is an Access problem.

Guess I should have stated that originally.

phranque

3:42 am on Nov 7, 2007 (gmt 0)

i'm no access expert but i would be surprised if access doesn't support standard sql syntax such as "group by".
(http://msdn2.microsoft.com/en-us/library/aa140011(office.10).aspx#acfundsql_sqlinaccess)
what error message do you get?