Solved

Duplicate records - Select based on latest date

Posted on 2009-04-07
2
316 Views
Last Modified: 2012-05-06
Given the following table:

ID  ItemNo     Datestamp
1   10           2009-03-27 15:58:18.000
2   10           2009-03-27 16:07:14.000
3   20           2009-03-27 16:17:15.000
4   30           2009-03-27 16:42:17.000
5   30           2009-03-27 16:33:33.000
6   40           2009-03-27 16:21:46.000
7   50           2009-03-27 16:49:18.000


Notice that the ID is unique (actually in my table it is a guid) and the ItemNo field has some duplicates.  

I would like to query all the fields in this table and select only the records with the latest datestamp - if there are multiple records with the same item number, get only 1 record for that ItemNo and choose the latest datestamp. How would I do that?

I have tried a few different queries using Group By, but have not been completely successful.


Thanks in advance,


Steve
0
Comment
Question by:scooper082898
[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
2 Comments
 
LVL 32

Accepted Solution

by:
bhess1 earned 500 total points
ID: 24091971
A query like this should return the data you are looking for....

SELECT m.*
FROM MyTable m
INNER JOIN (
    SELECT ItemNo,
        MAX(DateStamp) as MaxStamp
    FROM MyTable
    GROUP BY ItemNo
    ) Filter
    ON m.ItemNo = Filter.ItemNo
    AND m.DateStamp = MaxStamp
0
 

Author Closing Comment

by:scooper082898
ID: 31567755
Mch appreciated. I have never used "Filter on" before.
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how many extra RAM for SQL server is needed 23 48
TSQL mapping detailed records to group records 9 66
Query to return total 6 25
Addition to SQL for dynamic fields 6 56
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

733 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