selecting top( x ) multiple times
Sorry for the rubbish title but hopefully this will explain:
Given the table
name | data
---------------------
1 | 1000
1 开发者_StackOverflow中文版 | 2000
1 | 3000
2 | 1500
2 | 2500
2 | 3500
I want to be able to select the top( x ) for all names ordered by the data value. So if x = 2 the return will be
name | data
---------------------
1 | 2000
1 | 3000
2 | 2500
2 | 3500
;with cte AS
(
SELECT name, data, ROW_NUMBER() OVER (PARTITION BY name ORDER BY data DESC) AS RN
FROM YourTable
)
SELECT name, data
FROM cte
WHERE RN<=2
ORDER BY name, data
A slightly universal way would be (had not seen the edited tags that specified sql server)
Select
name,
data
From
<table> tbl
Where
data In
( Select Top 2 Distinct
data
From
<table>
Where
name = tbl.name
Order By
data Desc
)
精彩评论