开发者

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
     ) 
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜