Solved

Getting only top one most recent unique row

Posted on 2013-06-21
5
289 Views
Last Modified: 2013-06-21
I want to return the top most recent row containing a distinct modId, eliminating the duplicates.

I have a table:

id       modId
1           1
2            1
3            2
4            3
5            3
6            7
7            8
8            7


Notice the id is the unique id field of the table.
But the Modid can have multiple rows. I want to sort by the Id field descending in order to get the most recent row (yes, there is more data per row not included) and exclude the duplicate modids.

So my return results should be:

8,7,5,3,2 and the less recent duplicate rows will not be included.

id       modId
2            1
3            2
5            3
7            8
8            7

I only need to return the Id field, not the modId field if that helps, because my final query can include

select * from mytable where id IN (select id from yourquery)

How would I do that?

thanks!
0
Comment
Question by:BobCSD
5 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 450 total points
ID: 39266816
try the below code

select max(id),modId from mytable  group by modID

Open in new window

0
 
LVL 39

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 50 total points
ID: 39266832
Neo is correct, just adding a column name for the aggregate:

select max(id) as ID, modId from mytable  group by modID
0
 
LVL 5

Expert Comment

by:DOSLover
ID: 39266888
I think we need to associate the subquery to the main table as follows:
select * from mytable 
  where id IN 
         (select max(b.ID ) from mytable b where b.modId = a.modId group by modId)
    

Open in new window

0
 
LVL 1

Author Comment

by:BobCSD
ID: 39267139
I'll give them a shot and let you know! thanks!
0
 
LVL 1

Author Comment

by:BobCSD
ID: 39267196
thanks all! the first two worked!
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

In this article I will describe the Backup & Restore 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.
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.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

708 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

13 Experts available now in Live!

Get 1:1 Help Now