Solved

Duplicate records - Select based on latest date

Posted on 2009-04-07
2
292 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
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Testing connection to sql 7 58
SQL Select - Finding chars in a column 2 57
Insert from Stored Procedure where some field/s > 0 7 43
Query 14 54
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

895 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

11 Experts available now in Live!

Get 1:1 Help Now