How to get the Max(date) from a table

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?

JessyRobinson1234Asked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
97WideGlideCommented:
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
JessyRobinson1234Author Commented:
Perfect, thanks a lot!
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
97WideGlideCommented:
mwvisa1 - thank you very much for commenting on my solution.   I am learning also...  :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.