Return Top n with a tie

Question

Post reply

Return Top n with a tie

PhilM99

SSCrazy

Points: 2214
More actions
July 20, 2010 at 6:15 pm

#387398

Picture this:
DECLARE @test-2 TABLE(Item int, val int)
INSERT @test-2 SELECT 1, 90
INSERT @test-2 SELECT 2, 80
INSERT @test-2 SELECT 3, 70
INSERT @test-2 SELECT 4, 60
INSERT @test-2 SELECT 5, 50
INSERT @test-2 SELECT 6, 40
INSERT @test-2 SELECT 7, 30
INSERT @test-2 SELECT 8, 20
INSERT @test-2 SELECT 9, 10
SELECT TOP 5 Item, val FROM @test-2 ORDER BY VAL DESC
gives me the top 5 items by value (1,2,3,4,5)
1 90
2 80
3 70
4 60
5 50
but if the 6th, or 7th, or 8th (etc.) item, macthes the last one, I need to include it.
FOR example:
DECLARE @test-2 TABLE(Item int, val int)
INSERT @test-2 SELECT 1, 90
INSERT @test-2 SELECT 2, 80
INSERT @test-2 SELECT 3, 70
INSERT @test-2 SELECT 4, 60
INSERT @test-2 SELECT 5, 50
INSERT @test-2 SELECT 6, 50
INSERT @test-2 SELECT 7, 50
INSERT @test-2 SELECT 8, 20
INSERT @test-2 SELECT 9, 10
What I want is
1 90
2 80
3 70
4 60
5 50
6 50
7 50
Must be a simple way with going RBAR
Thanks Phil

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

This should do it

SELECT TOP (5) WITH TIES Item, val FROM @test-2 ORDER BY VAL DESC

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

PhilM99 SSCrazy Points: 2214 More actions · Answer 2

Thanks, that's it. Simple, I just didn't know about it... Phil

WayneS SSC Guru Points: 95460 More actions · Answer 3

@Ron,

Excellent! I see I need to look into WITH TIES a bit now.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Ben Teraberry SSChampion Points: 11845 More actions · Answer 4

Joe Celko (7/21/2010)
A more portable, ANSI/ISO Standard version, with ordinal functions:
CREATE TABLE Tests
(Item_nbr INTEGER NOT NULL PRIMARY KEY,
something_val INTEGER NOT NULL);
INSERT INTO Tests VALUES
(1, 90), (2, 80), (3, 70), (4, 60), (5, 50), (6, 40), (7, 30), (8, 20), (9, 10);
SELECT *
FROM (SELECT Item_nbr, something_val,
ROW NUMBER() OVER (ORDER BY something_val DESC) AS score
FROM Tests)
WHERE score <= @my_top_limit;

Aside from the syntax errors this won't work because he wants to return ties, which ROW_NUMBER will not do. This will work ...

SELECT *

FROM

(SELECT Item_nbr,

something_val,

DENSE_RANK() OVER (ORDER BY something_val DESC) AS score

FROM #Tests) sq

WHERE score <= 5;

I like the WITH TIES though ... I didn't know that existed.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

calvo SSChampion Points: 12930 More actions · Answer 5

bitbucket-25253 (7/20/2010)
This should do it
SELECT TOP (5) WITH TIES Item, val FROM @test-2 ORDER BY VAL DESC

Very cool! Didn't know about WITH TIES.

Thanks Mr. Bucket

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.