Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

Getting only top one most recent unique row

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
Starr Duskk
Asked:
Starr Duskk
2 Solutions
 
Surendra NathTechnology LeadCommented:
try the below code

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

Open in new window

0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
Neo is correct, just adding a column name for the aggregate:

select max(id) as ID, modId from mytable  group by modID
0
 
DOSLoverCommented:
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
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
I'll give them a shot and let you know! thanks!
0
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
thanks all! the first two worked!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now