[SOLVED] Max Command - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

[SOLVED] Max Command

Author

Topic

vtxvtx
Starting Member

18 Posts

Posted - 2012-09-04 : 11:40:31

Hi There. Another question.

I have a table with the following columns. ID (pk), ID2, Date, Cost.

What I want is to select from this table, ID2, MAX(Date) and the Cost relating to the MAX(Date) [Group by ID2]. It's the Cost bit that i'm not sure how to get at.

Whats the easiest way to get to this?

Thanks,
Andy

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-09-04 : 12:18:03

can you give an example of your data!

--------------------------
Joins are what RDBMS's do for a living

vtxvtx
Starting Member

18 Posts

Posted - 2012-09-04 : 13:55:53

Example Table


ID  |ID2 |DATE  |COST
----|----|------|----
1   |1   |17 Mar|17
2   |1   |30 Mar|12
3   |2   |18 Mar|18
4   |2   |30 Mar|31
5   |3   |12 Mar|5

What i'd want to return is:


ID  |ID2 |DATE  |COST
----|----|------|----
2   |1   |30 Mar|12
4   |2   |30 Mar|31
5   |3   |12 Mar|5

That is only the rows where the date is the most recent for each of ID2, i hope that makes sense

Andy

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-09-04 : 14:42:37

SELECT select ID,ID2,Date,Cost
FROM
(select ID,ID2,Date,Cost, row_number() over(partition by ID order by date desc) as MaxDate
from yourTable
) a

WHERE a.MaxDate = 1

Jim

Everyday I learn something that somebody else already knew

arpana patil
Starting Member

24 Posts

Posted - 2012-09-05 : 01:58:37

select [ID],[ID2],[DATE],[COST]
from [dbo].[GetMaxId] a
where [DATE]=(select MAX([DATE]) from [dbo].[GetMaxId] b where a.[ID2]=b.[ID2])
order by [ID2]

vtxvtx
Starting Member

18 Posts

Posted - 2012-09-05 : 09:28:17

Thank You arpana patil, that worked great.

Andy

arpana patil
Starting Member

24 Posts

Posted - 2012-09-28 : 07:33:40

Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources