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.
| Author |
Topic |
|
gwguy7
Starting Member
3 Posts |
 Posted - 2011-07-07 : 09:42:44
|
I need some help for formulating this query:Currently I have a query which pulls data from 2-3 tables for the cusips that I asked for. The data looks like this:Cusip Price Data_enteredaa1 101.1 05/06aa1 100.2 05/03aa1 99.2 05/02aa1 102.2 04/25ba1 45.5 06/01ba1 46.8 05/25ba1 43.0 05/20ba1 42.0 05/15I want to further pull only three latest records for each cusips from this dataset. So the output will look like:aa1 101.1 05/06aa1 100.2 05/03aa1 99.2 05/02ba1 45.5 06/01ba1 46.8 05/25ba1 43.0 05/20Can someone help me with this? For some reason the "top" keyword doesnt work in the sql tool that I use. Thanks !! |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-07-07 : 09:48:34
|
| select * from(select *, seq = row_number() over (partition by cusip order by data_entered desc))where seq <= 3order by cusip, data_entered==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
gwguy7
Starting Member
3 Posts |
Posted - 2011-07-07 : 10:42:42
|
| For some reason the "row_number() over (partition by cusip order by data_entered desc)" doesnt work for me. This is how I used your query:-----select cusip,rownum over-------Right now I get only 3 records for one cusip. I have to somehow get to use the partition clause. Any ideas? |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-07-07 : 11:17:52
|
| Are you using sql server?Which version?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
Top three records for each cusip