sub query help

[tjc19999]

Hello everyone, I need some help figuring out the proper subquery for my select statement.

 

Here are 2 codes that I have:

$result = mysql_query("SELECT * FROM applications WHERE history=(select MAX(history) FROM applications)");

$result = mysql_query("SELECT DISTINCT id FROM applications");

 

Both of these queries work, but what I need is a subquery that will

1) select DISTINCT id

2) for each distinct ID select max(history)

 

Thank you in advance if you can help.

[kickstart]

Hi

 

Something like this should do it

 

SELECT *
FROM applications a
INNER JOIN (SELECT id, MAX(history) AS maxHistory FROM applications GROUP BY id) b
ON a.id = b.id AND a.history = b.maxHistory

 

All the best

 

Keith

[tjc19999]

That most certainly did the trick...a bit more complex than anything I have come across as of yet.  Thank you very much...you have ended a long day of hitting my head against the desk.