Group by and latest record - 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

Group by and latest record

Author

Topic

hayashiryo
Starting Member

46 Posts

Posted - 2013-08-08 : 00:18:58

Hi all,

Need some help to formulate a query. Here's what I'm trying to do


TransID		MemberID	FundID	BuyPrice	Date
1		501		101	$104		2013-01-24
2		501		102	$101		2013-04-23
3		501		102	$118		2013-02-04
4		501		102	$98		2013-05-19
5		501		103	$53		2013-05-21
6		501		103	$39		2013-07-09

I'm trying to get the latest buy price for each fund a member bought.

So in the above example, I'm trying to formulate a query to retrieve all the latest bought price for memberid 105:

Desire Query Result


TransID		MemberID	FundID	BuyPrice	Date
1		501		101	$104		2013-01-24
4		501		102	$98		2013-05-19
6		501		103	$39		2013-07-09

So far, I've tried using Group By clause on FundID and BuyPrice and MAX() function for the date, but I can' get the TransID.

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-08 : 01:00:43

[code]
SELECT TransID,
MemberID,
FundID,
BuyPrice,
Date
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY MemberID,FundID ORDER BY Date DESC) AS RN
FROM Table
)t
WHERE RN=1
[/code]

If you've multiple rows for same maximum date for same fund and want all of them use DENDE_RANK instead of ROW_NUMBER in the above query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

hayashiryo
Starting Member

46 Posts

Posted - 2013-08-08 : 08:48:05

Thanks! That worked nicely.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-08 : 09:35:16

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources