-
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.
-
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.
Last edited by PAVB; 04-29-2010 at 04:17 PM.
Reason: typo
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|