Solved

Selecting all records that have a max value in another column?

Posted on 2008-11-02
4
384 Views
Last Modified: 2009-07-18
Hi there,
I have a table where I want to retrieve all the records that have a maximum modified date for a particular fact:

FACT_ID, FACT1, MODIFIED_DATE ... (100 other facts)
1, A, 2008-01-01 00:00:00.000
2, A, 2008-02-01 00:00:00.005
3, A, 2008-02-01 00:00:00.005
4, B, 2007-01-01 00:00:00.000
5, B, 2008-05-05 00:00:00.000

I would like the query to return rows 2, 3, and 5.. I've only been able to do this using a cursor so far iterating through all the fact_ids, but I'm sure there must be a set operation that can do this :)

Thanks,

Charles
0
Comment
Question by:cshlin
  • 2
4 Comments
 
LVL 9

Expert Comment

by:jamesgu
ID: 22863606
select * from  a,
inner join (select FACT_ID, FACT1, max(MODIFIED_DATE) as max_MODIFIED_DATE from  group by FACT_ID, FACT1) b
where a.FACT_ID  = b. FACT_ID
and a. FACT1  = b. FACT1  
and a. MODIFIED_DATE = b. max_MODIFIED_DATE
0
 
LVL 9

Accepted Solution

by:
jamesgu earned 63 total points
ID: 22863658
try this one instead of the previous

select * from  a,
inner join (select FACT1, max(MODIFIED_DATE) as max_MODIFIED_DATE from  group by FACT1) b
where a.FACT_ID  = b. FACT_ID
and a. MODIFIED_DATE = b. max_MODIFIED_DATE
0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 62 total points
ID: 22863851
Try this:

with RankedData as
(select *,dense_rank() over(partition by fact1 order by modified_date desc) dr from YourTable)
select * from RankedData
where dr=1
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

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

839 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