?
Solved

Getting only top one most recent unique row

Posted on 2013-06-21
5
Medium Priority
?
323 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:Starr Duskk
5 Comments
 
LVL 16

Accepted Solution

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

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

Open in new window

0
 
LVL 41

Assisted Solution

by:Kyle Abrahams
Kyle Abrahams earned 200 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 2

Author Comment

by:Starr Duskk
ID: 39267139
I'll give them a shot and let you know! thanks!
0
 
LVL 2

Author Comment

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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

600 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