November 16, 2009 at 5:03 am
Hi All
I'm trying to build a query that will filter the top 10 values. Noting that we never know what the values in the list will be. The values below in the Before column should filter to After column. Thanks for you assistance.
Asta 🙂
Before After
Rank Rank
3 33
3 34
3 35
7 36
7 37
7 38
7 40
8 40
9 41
12 45
12 45
12 45
13 45
15 46
15
16
17
19
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
40
40
41
45
45
45
45
46
November 16, 2009 at 5:22 am
Is this you are trying.
DECLARE @TABLE TABLE(I INT )
INSERT INTO @TABLE SELECT 3
INSERT INTO @TABLE SELECT 3
INSERT INTO @TABLE SELECT 3
INSERT INTO @TABLE SELECT 7
INSERT INTO @TABLE SELECT 7
INSERT INTO @TABLE SELECT 7
INSERT INTO @TABLE SELECT 7
INSERT INTO @TABLE SELECT 8
INSERT INTO @TABLE SELECT 9
INSERT INTO @TABLE SELECT 12
INSERT INTO @TABLE SELECT 12
INSERT INTO @TABLE SELECT 12
INSERT INTO @TABLE SELECT 13
INSERT INTO @TABLE SELECT 15
INSERT INTO @TABLE SELECT 15
INSERT INTO @TABLE SELECT 16
INSERT INTO @TABLE SELECT 17
INSERT INTO @TABLE SELECT 19
INSERT INTO @TABLE SELECT 19
INSERT INTO @TABLE SELECT 20
INSERT INTO @TABLE SELECT 21
INSERT INTO @TABLE SELECT 22
INSERT INTO @TABLE SELECT 23
INSERT INTO @TABLE SELECT 24
INSERT INTO @TABLE SELECT 25
INSERT INTO @TABLE SELECT 26
INSERT INTO @TABLE SELECT 27
INSERT INTO @TABLE SELECT 28
INSERT INTO @TABLE SELECT 29
INSERT INTO @TABLE SELECT 30
INSERT INTO @TABLE SELECT 31
INSERT INTO @TABLE SELECT 32
INSERT INTO @TABLE SELECT 33
INSERT INTO @TABLE SELECT 34
INSERT INTO @TABLE SELECT 35
INSERT INTO @TABLE SELECT 36
INSERT INTO @TABLE SELECT 37
INSERT INTO @TABLE SELECT 38
INSERT INTO @TABLE SELECT 40
INSERT INTO @TABLE SELECT 40
INSERT INTO @TABLE SELECT 41
INSERT INTO @TABLE SELECT 45
INSERT INTO @TABLE SELECT 45
INSERT INTO @TABLE SELECT 45
INSERT INTO @TABLE SELECT 45
INSERT INTO @TABLE SELECT 46
Select * from @TABLE WHERE I IN(
Select TOP 15 I FROM @TABLE ORDER BY I DESC )
Thanks
Parthi
November 16, 2009 at 6:20 am
Hi thank you for your reply, I dont think just using TOP 15 will work as the list of values will always be different hence I need a way to automaically pull the top 10 values every time.
Thanks
Asta
November 16, 2009 at 8:10 am
Reluctantly I am posting this as I am sure there IS a better way of doing it. But I could not think of any other thing apart from this now, see if it helps. (if you are looking for a better performing query wait till someone comes up with a killer one!). Thanks
Select t1.I from @table t1
JOIN ( Select *, row_number() OVER (order by I desc) as row_no from
(Select DISTINCT I from @table) t) t2
ON t1.I = t2.I
Where row_no < = 10
By the way I borrowed the test data from previous post. Thanks for that.
---------------------------------------------------------------------------------
November 16, 2009 at 1:35 pm
Thank, thats exactly what I want 🙂
Asta
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply