top without using TOP

Question

Post reply

top without using TOP

Forum Etiquette: How to post Reporting Services problems

[/url]

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

How to Post Performance Problems - by Gail Shaw
[/url]

Richard Warr SSCertifiable Points: 6966 More actions · Answer 1

Learner1 (11/15/2010)
Hi,
Can I get the 10th highest salary without using TOP or RowNumber() over partition?

At the interview you should hold out for the 5th highest salary. This will give you room for negotiation.

BTW I liked the IDENTITY answer best.

GSquared SSC Guru Points: 260824 More actions · Answer 2

Learner1 (11/15/2010)
Here is the complete code with inserts... to find 3rd highest salary
try it..
CREATE TABLE [SAL](
[name] [varchar](12) NULL,
[salary] [int] NULL
)
select * from SAL
INSERT INTO sal ( name,salary) VALUES ( 'LARY', 10)
INSERT INTO sal ( name,salary) VALUES ( 'TIA', 20)
INSERT INTO sal ( name,salary) VALUES ( 'GARY', 30)
INSERT INTO sal ( name,salary) VALUES ( 'ANNROSE', 40)
INSERT INTO sal ( name,salary) VALUES ( 'RAMA', 50)
INSERT INTO sal ( name,salary) VALUES ( 'TAMY', 60)
INSERT INTO sal ( name,salary) VALUES ( 'RAGH', 70)
SELECT DISTINCT (a.salary) FROM SAL A WHERE 3=
(SELECT COUNT (DISTINCT (b.salary)) FROM SAL B WHERE a.salary<=b.salary)

The problem with this is it will work well on a VERY small table with only a few rows, but when you have a large number of rows, it will stink like a sulfur mill.

Yes, it looks like a clever trick. So is driving from LA to New York in reverse. Make sure the guy asking you for this understands that it's a non-scalable solution and should only be used as a conversation piece, not ever in production code. Take a look online for "triangular joins" and you'll find out why.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

;with ranked_data as

(

select name,salary,DENSE_RANK() OVER(order by salary,name,(SELECT X FROM (SELECT 1) a(a) CROSS APPLY(SELECT RAND(CHECKSUM(NEWID()))) b(X)) ) as position

from SAL

)

select *

from ranked_data

where position =3

This will replicate the ROW_NUMBER() function even for duplicate rows (the cross apply does this for us), so you just select the position you want.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

CREATE TABLE #SAL(

[name] [varchar](12) NULL,

[salary] [int] NULL

)

INSERT INTO #sal ( name,salary) VALUES ( 'LARY', 10)

INSERT INTO #sal ( name,salary) VALUES ( 'TIA', 20)

INSERT INTO #sal ( name,salary) VALUES ( 'GARY', 30)

INSERT INTO #sal ( name,salary) VALUES ( 'ANNROSE', 40)

INSERT INTO #sal ( name,salary) VALUES ( 'RAMA', 50)

INSERT INTO #sal ( name,salary) VALUES ( 'TAMY', 60)

INSERT INTO #sal ( name,salary) VALUES ( 'RAGH', 70)

SET ROWCOUNT 3

SELECT *

FROM #sal

ORDER BY Salary DESC

😛

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mister.magoo SSC-Forever Points: 47174 More actions · Answer 5

@chris-2 - but how do you select only the 3rd Salary?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

mister.magoo (11/16/2010)
@Chris - but how do you select only the 3rd Salary?

Very quickly!

SET ROWCOUNT 3

SELECT *

INTO #Magoo

FROM #sal

ORDER BY Salary DESC

SET ROWCOUNT 1

SELECT *

FROM #Magoo

ORDER BY Salary

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mister.magoo SSC-Forever Points: 47174 More actions · Answer 7

I don't know if I like being hashed like that! :hehe:

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

mister.magoo (11/16/2010)
I don't know if I like being hashed like that! :hehe:

LOL! It's a gate, mate 😉

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden