Geek Answers Handbook

Select vertex (x) several times

Sorry for the garbage title, but hopefully this will explain:

Given a table

name | data --------------------- 1 | 1000 1 | 2000 1 | 3000 2 | 1500 2 | 2500 2 | 3500

I want to be able to select the top (x) for all names sorted by data value. Therefore, if x = 2, then the return will be

  name | data --------------------- 1 | 2000 1 | 3000 2 | 2500 2 | 3500
+4
source share
2 answers
 ;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
+9
source

A slightly universal way would be (I didn’t see the edited tags that the sql server indicated)

 Select name, data From <table> tbl Where data In ( Select Top 2 Distinct data From <table> Where name = tbl.name Order By data Desc )
+2
source

More articles:


All Articles