rows with largest date
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: rows with largest date

  1. #1
    Join Date
    Apr 2010
    Posts
    2

    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.

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    What have you tried so far (on your own)?

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    a subquery where you select all the rows with max(date) in

  4. #4
    Join Date
    Apr 2010
    Posts
    2
    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.

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by fredsnertz View Post
    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
  •  



Click Here to Expand Forum to Full Width