rows with largest date

I have a table where one column is a date. How can I get all rows that contain the largest date in the table? I don't know how many rows to expect or what the largest date is.

If my data is
name | date
--------------------------------------------------
dog | 2010-03-20 20:10:10
cat | 2010-04-26 10:10:10
bird | 2010-04-25 23:59:59
rat | 2010-04-26 10:10:10
gnat | 2010-04-02 06:07:08

I would want my query to return:
cat | 2010-04-26 10:10:10
rat | 2010-04-26 10:10:10

Thanks.

What have you tried so far (on your own)?

a subquery where you select all the rows with max(date) in

I can do it with 2 queries, but I was hoping to do it with one. I guess I can combine them into 1 this way:

SELECT * from tables WHERE name='dog' and date = (SELECT max(date) FROM table WHERE name='dog')

This isn't exactly how I specified the requirements in my original post but I'm sure you guessed that I was simplifying my data.

Quote:

---

Originally Posted by fredsnertz

I can do it with 2 queries, but I was hoping to do it with one. I guess I can combine them into 1 this way:

SELECT * from tables WHERE name='dog' and date = (SELECT max(date) FROM table WHERE name='dog')

---

This is exactly - kind of - what Dave suggested, a subquery a.k.a. inline view.

Technically you have just one query which includes an inline view.

By the way, your query works... providing you only want to retrieve the dog with the max(date) - you can use the same concept to serve your original specs.