Multiple Select Max Statements 2

[Extension]

Hi,

I want to select the lastest date from a table. But since alot of records have the same date, I end up with multiple records and I simply want to get one record. So I want to select the max date and also the max ID (which is the primary key)

Current Query

SELECT A.*
FROM PROFILES AS A
WHERE A.ACTIVE = 1 AND A.PROFILE_TYPE = 'TEST'
AND A.DATE = (SELECT Max(B.DATE) FROM PROFILES AS B WHERE B.ACTIVE = 1 AND B.PROFILE_TYPE = 'TEST')

Thank you for the help of PHV on my last post.

 

[dhookom]

I can only guess that you have a primary key field ID in the table. Try this:

SELECT A.*
FROM PROFILES As A
WHERE A.Active = 1 AND A.PROFILE_TYPE = 'TEST'
AND A.ID = 
(SELECT TOP 1 ID
 FROM PROFILES B
 WHERE B.ACTIVE = 1 AND B.PROFILE_TYPE='TEST'
 ORDER BY [DATE] DESC, ID DESC)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[r937]

select [b]TOP 1[/b] *
  from PROFILES 
 where ACTIVE = 1 
   and PROFILE_TYPE = 'TEST'
[b]order 
    by [DATE] desc
     , ID desc[/b]

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)