How to get the Max(date) from a table

Posted on 2008-11-13
Last Modified: 2012-08-13
I have 2 tables:

Config_Key (PK)

Config_Key (FK)

The same files get uploaded a couple of times a day and I am writing a stored procedure to display the  latest update:

Config_Key        File_Title          Uploaded_Date_Time
20                       Sun.xls            2008-11-12 14:44:24.630
20                       Sun.xls            2008-11-13 13:06:24.627

My question is how can I display the second row only?

Question by:JessyRobinson1234
    LVL 59

    Expert Comment

    by:Kevin Cross
    For SQL 2005, try something like this:
    ;WITH uploads AS (
    SELECT c.*, h.Uploaded_Date_Time
    , row_number() OVER (PARTITION BY c.Config_Key ORDER BY h.Uploaded_Date_Time DESC) AS Row_Number
    FROM Upload_Config c
    INNER JOIN Upload_History h ON c.Config_Key = h.Config_Key
    SELECT Config_key
    , File_Title
    , Upload_Date_Time
    FROM uploads
    WHERE Row_Number = 1
    ORDER BY Config_Key

    Open in new window

    LVL 59

    Accepted Solution

    You could also do like this:
    (forgive my type-o above as well as Upload_Date_time should be Uploaded_Date_Time)
    SELECT c.*, h.Uploaded_Date_Time
    FROM Upload_Config c
    INNER JOIN (SELECT Config_Key, MAX(Uploaded_Date_Time) AS Uploaded_Date_Time FROM Upload_History GROUP BY Config_Key) h ON c.Config_Key = h.Config_Key

    Open in new window

    LVL 8

    Expert Comment

    Wouldn't this do it ?

    Select upload_config.config_key, file_title, max(uploaded_date_time)
      upload_config, upload_history
      upload_config.config_key = upload_history.config_key
    group by config_key;

    Author Closing Comment

    Perfect, thanks a lot!
    LVL 59

    Expert Comment

    by:Kevin Cross
    97WIdeGlide, that would work in this case if you also group by the file_title.  I chose to not use that method so this can fit cases where there are a lot more columns involved that two. :)

    I would use the ANSI standard for joining though instead of cartesian join as you have it.
    LVL 8

    Expert Comment

    mwvisa1 - thank you very much for commenting on my solution.   I am learning also...  :)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now