Max() conundrum

Question

Max() conundrum

JimS-Indy

SSCarpal Tunnel

Points: 4514
More actions
November 9, 2011 at 8:33 am

#251520

I have a table (ID identity,ProjectID foreign key,MICAPID foreign key,ODD datetime...) called tblxrefProjectMICAP. ProjectID+MICAPID is a unique key. A project may have multiple MICAPs, and a MICAP may have multiple projects.
I need to develop a query that identifies the MICAP and ODD (and other fields) from the most recent row having a given ProjectID. Since a project may have multple MICAPs, I just need to identify the most recent (based on ODD).
I know you guys are creative. How about it?
Jim

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

JimS-Indy (11/9/2011)
I have a table (ID identity,ProjectID foreign key,MICAPID foreign key,ODD datetime...) called tblxrefProjectMICAP. ProjectID+MICAPID is a unique key. A project may have multiple MICAPs, and a MICAP may have multiple projects.
I need to develop a query that identifies the MICAP and ODD (and other fields) from the most recent row having a given ProjectID. Since a project may have multple MICAPs, I just need to identify the most recent (based on ODD).
I know you guys are creative. How about it?

Hello and welcome to SSC!

It seems that your DDL script has become detached from your post, or perhaps you were unaware of the benefits of providing one.

When you have time, please read this article[/url] about the best way to provide us with working sample data and DDL scripts. This will allow the unpaid volunteers of this site to provide you with working, tested code for your particular problem.

Thanks.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Mark Cowne One Orange Chip Points: 26968 More actions · Answer 2

Something like this, using ROW_NUMBER()

WITH CTE AS (

SELECT *,

ROW_NUMBER() OVER(PARTITION BY ProjectID ORDER BY ODD DESC) AS rn

FROM tblxrefProjectMICAP)

SELECT *

FROM CTE

WHERE rn=1;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

JimS-Indy SSCarpal Tunnel Points: 4514 More actions · Answer 3

I considered including the DDL script, but decided it would needlesly complicate the question, since it is likely 20 lines long. I got my answer anyway.

I do include DDL scripts whenever I feel they add value or clarity.

Jim

JimS-Indy SSCarpal Tunnel Points: 4514 More actions · Answer 4

Thank you muchly. some day I'll learn that syntax.

Jim