Solved

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

Posted on 2008-11-02
4
366 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Date Range Syntax Access 2003 10 53
SQL query 4 46
Query to Add Late Tolerance 10 60
sql query Help 12 52
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.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

930 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