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 |
|
newcents2
Starting Member
1 Post |
 Posted - 2014-03-12 : 00:58:34
|
| The data as it's recorded in the SQL table looks like this:ID batch date_time company_name symbol score46707 1 3/11/14 9:30 AM J.C. PENNEY COMPANY INC. JCP 9746708 1 3/11/14 9:30 AM ZOGENIX INC ZGNX 9746709 1 3/11/14 9:30 AM EXCO RESOURCES INC XCO 9346710 1 3/11/14 9:30 AM MARVELL TECHNOLOGY GROUP LTD MRVL 9146711 1 3/11/14 9:30 AM CABOT OIL & GAS CORP COG 9046775 2 3/11/14 9:31 AM MARVELL TECHNOLOGY GROUP LTD MRVL 9746776 2 3/11/14 9:31 AM CABOT OIL & GAS CORP COG 9646777 2 3/11/14 9:31 AM SENOMYX INC SENO 9346778 2 3/11/14 9:31 AM J.C. PENNEY COMPANY INC. JCP 9146779 2 3/11/14 9:31 AM ZOGENIX INC ZGNX 9146780 2 3/11/14 9:31 AM EXCO RESOURCES INC XCO 9046781 2 3/11/14 9:31 AM INFOSONICS CORP IFON 89Out of that data, I need to run a query that shows the following result set:ID batch date_time company_name symbol score46707 1 3/11/14 9:30 AM J.C. PENNEY COMPANY INC. JCP 9746708 1 3/11/14 9:30 AM ZOGENIX INC ZGNX 9746709 1 3/11/14 9:30 AM EXCO RESOURCES INC XCO 9346775 2 3/11/14 9:31 AM MARVELL TECHNOLOGY GROUP LTD MRVL 9746776 2 3/11/14 9:31 AM CABOT OIL & GAS CORP COG 9646777 2 3/11/14 9:31 AM SENOMYX INC SENO 93Basically what I'm after, I need to pull only the top 3 records based on score in descending order BUT these should be grouped by batch. For example, I need to select the top 3 records which have the highest score from batch 1, 2, 3, etc. where there could be any number of records in a batch and any number of batches.TIA! |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2014-03-12 : 02:32:40
|
[code]select *from( select *, rn = row_number() over (partition by batch order by score desc) from yourtable) dwhere d.rn <= 3[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
|
|
|
select query issue - get top 3 for repeating area