Solved

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

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Running Total in Access 4 49
Please help for the below sql query. 1 24
Run SQL Server Proc from Access 11 31
T-SQL: New to using transactions 9 31
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 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.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

803 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