?
Solved

How to get the Max(date) from a table

Posted on 2008-11-13
6
Medium Priority
?
251 Views
Last Modified: 2012-08-13
I have 2 tables:

Upload_Config
Config_Key (PK)
File_Title

Upload_History
Config_Key (FK)
Uploaded_Date_Time

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

Example:
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?

0
Comment
Question by:JessyRobinson1234
  • 3
  • 2
6 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22951839
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

0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 22951874
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

0
 
LVL 8

Expert Comment

by:97WideGlide
ID: 22951900
Wouldn't this do it ?

Select upload_config.config_key, file_title, max(uploaded_date_time)
from
  upload_config, upload_history
where
  upload_config.config_key = upload_history.config_key
group by config_key;
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Closing Comment

by:JessyRobinson1234
ID: 31516444
Perfect, thanks a lot!
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22952255
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.
0
 
LVL 8

Expert Comment

by:97WideGlide
ID: 22952633
mwvisa1 - thank you very much for commenting on my solution.   I am learning also...  :)
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

864 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