Solved

SQL Query to display last row inserted

Posted on 2015-01-09
8
128 Views
Last Modified: 2015-01-09
I have a SQL 2005 Database. I'm looking for a relatively simple query to do the following. This is a very simplified example of the table. There are obviously more than two unique SystemNumbers.

TableName is Data

DataID         SystemNumber       TimeStamp
27765178    8001                          11:00 AM
27765179    8001                          11:02 AM
....
2777192      8555                          10:32 AM
2777193      8555                          10:37 AM

I need a query that can be run that will display the results like so:

DataID       SystemNumber       TimeStamp
27765178  8001                          11:02 AM
2777193    8555                          10:37 AM

So the most recent TImeStamp for each unique SystemNumber.

Any suggestions on how I can do this?

Eventually I will be using this on some SQL 2000 (I know) servers too, and putting into SQL Server Reporting
0
Comment
Question by:KThrace
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 40540361
>So the most recent TImeStamp for each unique SystemNumber.
Give this a whirl.
Subquery gets the max time stamp for a given SystemNumber
Main query gets all rows in Data that match the above two values.
SELECT d.DataID, d.SystemNumber, dm.time_max as TimeStamp
FROM Data d
   JOIN (SELECT SystemNumber, Max(TimeStamp) as time_max 
         FROM Data
         GROUP BY SystemNumber) dm ON d.SystemNumber = dm.SystemNumber AND d.TimeStamp = dm.time_max
ORDER BY d.SystemNumber

Open in new window


For more examples see SQL Server GROUP BY Solutions,
0
 

Author Comment

by:KThrace
ID: 40540402
Beautiful! Works on 2005 AND 2000! I'll accept as a solution but as a follow up...

...for whatever reason in the table I have different DataID but same SystemNumber and EXACTLY same TimeStamp - so the result actually brings back two rows for each unique systemnumber. Is there a quick and easy way to add a statement such that:

If SystemNumber AND TimeStamp are the same return only one result
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40540410
How would you determine which 'one' to display?  Min(DataID), Max(DataID), I don't care just pick one, ...
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:KThrace
ID: 40540411
Max(DataID)
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40540436
Try this then.
SELECT Max(d.DataID), d.SystemNumber, dm.time_max as TimeStamp
FROM Data d
   JOIN (SELECT SystemNumber, Max(TimeStamp) as time_max 
         FROM Data
         GROUP BY SystemNumber) dm ON d.SystemNumber = dm.SystemNumber AND d.TimeStamp = dm.time_max
GROUP BY d.SystemNumber, dm.time_max
ORDER BY d.SystemNumber

Open in new window

0
 

Author Comment

by:KThrace
ID: 40540452
Jim, it's perfect!

Thank you so much!
0
 

Author Closing Comment

by:KThrace
ID: 40540455
Perfect and elegant!
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40540461
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

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!

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

739 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