SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 [SOLVED] Max Command		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vtxvtx
Starting Member

18 Posts
Posted - 09/04/2012 :  11:40:31  Show Profile  Reply with Quote
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
Edited by - vtxvtx on 09/05/2012 09:28:51

xhostx
Constraint Violating Yak Guru

USA
261 Posts
Posted - 09/04/2012 :  12:18:03  Show Profile  Reply with Quote
can you give an example of your data!

--------------------------
Joins are what RDBMS's do for a living
Go to Top of Page

vtxvtx
Starting Member

18 Posts
Posted - 09/04/2012 :  13:55:53  Show Profile  Reply with Quote
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
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2868 Posts
Posted - 09/04/2012 :  14:42:37  Show Profile  Reply with Quote
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
Edited by - jimf on 09/04/2012 14:43:10
Go to Top of Page

arpana patil
Starting Member

India
24 Posts
Posted - 09/05/2012 :  01:58:37  Show Profile  Reply with Quote
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]
Go to Top of Page

vtxvtx
Starting Member

18 Posts
Posted - 09/05/2012 :  09:28:17  Show Profile  Reply with Quote
Thank You arpana patil, that worked great.

Andy
Go to Top of Page

arpana patil
Starting Member

India
24 Posts
Posted - 09/28/2012 :  07:33:40  Show Profile  Reply with Quote
Welcome
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000