sql query question

[firefoxinc]

I am working on a query for a php/sql database I am building.

SELECT
users.ID,
users.username,
users.authlevel,
rankdate.rdrid,
rankdate.rddate
FROM
users ,
rankdate
WHERE
rankdate.rduid = users.ID

This is what it would return

id username authlevel rdrid rddate
1 FireFox 8 O-4 2009-05-20
1 FireFox 8 O-3 2009-05-15
2 Xyekep 8 O-2 2009-07-26


Now the timestamp is there date of rank. The rankdate table records when they where promoted to what rank. FireFox has 2 entrys so it shows up twice. Is there a way so set the query up so it only displayes the most resent date of rank entry. Example

1 FireFox 8 O-4 2009-05-20
2 Xyekep 8 O-2 2009-07-26

I would like to the whole" while($row = mysql_fetch_row($query))" to make a simple list of everyone in the database and there current rank.

Any tips or suggestions on how I could do this?

[Old Pedant]

SELECT users.ID, users.username, users.authlevel, rankdate.rdrid, rankdate.rddate
FROM users, rankdate, (
SELECT rduid, MAX(rddate) AS maxdate
FROM rankdate GROUP BY rduid ) AS RD
WHERE rankdate.rduid = users.ID
AND rankdate.rduid = RD.rduid
AND rankdate.rddate = RD.maxdate


Or you could first create a VIEW for that inner SELECT and then join to it. Or or or...

[firefoxinc]

Ooo it works.

I had to sit there for 10 min looking at it to figure out what exactly it was doing.

Thank you for the help.

[Old Pedant]

> I had to sit there for 10 min looking at it ...

LOL! Sorry! I was in a hurry.

When you only need *one* piece of information about the MAX or MIN or FIRST or whatever record in a group, it's easy. You just use a SELECT similar to the inner select that I used there.

But when you need two (or more) pieces of information from the same record, as you did, then you have to do an inner select to first find the max for each group and then join back to the original table to get the other info. Yours was only slightly complicated by the need to then join to your separate users table.

It's a more or less standard technique, well worth putting in your bag of tricks. Best of luck!