top without using TOP

Question

Post reply

top without using TOP

sql_learner29

SSCrazy Eights

Points: 9107
More actions
November 15, 2010 at 1:05 pm

#228801

Hi,
Can I get the 10th highest salary without using TOP or RowNumber() over partition?
[font="Comic Sans MS"]
---------------------------------------------------
Thanks [/font]

Viewing 15 posts - 1 through 15 (of 22 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

You could do something stupid like:

select max(Salary)

from MyTable

where Salary <

(select max(Salary)

from MyTable

where Salary <

(select max(Salary)

from MyTable

where ...

Keep going nine levels deep on the sub-queries and you'll get the 10th highest. You could use that as a sub-query with an equality test and you'd get all the ties for 10th highest.

However, it's a really dumb idea. I'd use Top or Rank/Dense Rank/Row Number. That's what they're for.

- 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

GSquared SSC Guru Points: 260824 More actions · Answer 2

Oh, and if this is for homework, you could also potentially use a loophole in the exact wording of the question, and build a cursor with an Order By in the query, and step through till you find the 10th time the salary value goes down (to account for ties). Would be another messy, but "legal" way to do this.

Again, like the nested sub-queries, I'd never use it in a production system.

Is this homework? That kind of requirement makes it sound like it.

- 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

WayneS SSC Guru Points: 95460 More actions · Answer 3

What do you want to do if multiple people have the same salary?

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

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 4

thanks ,,,,,,,no it's not a home work

its again an interview question but I got an answer already...

this can be done using a corelated query

SELECT DISTINCT (a.age) FROM test55 A WHERE 10=

(SELECT COUNT (DISTINCT (b.age)) FROM test55 B WHERE a.age<=b.age)

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

Learner1 (11/15/2010)
thanks ,,,,,,,no it's not a home work
its again an interview quetion but I got an answer already...
this can be done using a corelated query
SELECT DISTINCT (a.age) FROM test55 A WHERE 2=
(SELECT COUNT (DISTINCT (b.age)) FROM test55 B WHERE a.age<=b.age)

Good luck on that interview; with an answer like that, you're going to need it.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 6

Learner1 (11/15/2010)
thanks ,,,,,,,no it's not a home work
its again an interview question but I got an answer already...
this can be done using a corelated query
SELECT DISTINCT (a.age) FROM test55 A WHERE 10=
(SELECT COUNT (DISTINCT (b.age)) FROM test55 B WHERE a.age<=b.age)

You're getting a unique count of ages where the count of distinct ages is... :crazy: Wut?

I wish you luck. Please note I will be available for remote consulting in early January after the holidays when you find you need more support.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 7

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)

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

LutzM SSC Guru Points: 107049 More actions · Answer 8

@Craig: I like your reply regarding your availability 😀

@Learner1: insert the data into a table with an IDENTITY column and do a SELECT WHERE ID= 3 😛

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 9

HAHAHA

very funny

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

LutzM SSC Guru Points: 107049 More actions · Answer 10

Learner1 (11/15/2010)
HAHAHA
very funny

What's so funny?

Based on the "rules" you prvided, the IDENTITY approach is valid.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 11

LutzM (11/15/2010)
Learner1 (11/15/2010)
HAHAHA
very funny
What's so funny?
Based on the "rules" you prvided, the IDENTITY approach is valid.

I believe that was aimed at me, Lutz, not you. 😀

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 12

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

Since you specifically mentioned not to use RowNumber() over partition I am getting the result just by using ROW_NUMBER() 😛

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)

--Your Query

SELECT DISTINCT (a.salary) FROM SAL A WHERE 3=

(SELECT COUNT (DISTINCT (b.salary)) FROM SAL B WHERE a.salary<=b.salary)

--My Query

select salary from

(

select *,ROW_NUMBER()over(order by salary desc)rid from SAL

) t where rid=3

drop table SAL

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 13

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

Since this is a stupid nonsensical restriction 😉 , heres mine

Use denali

select * from #SAL

ORDER BY salary ASC

OFFSET 2 ROWS

FETCH NEXT 1 ROWS ONLY

Clear Sky SQL
My Blog[/url]

LutzM SSC Guru Points: 107049 More actions · Answer 14

Dave Ballantyne (11/16/2010)
Learner1 (11/15/2010)
Hi,
Can I get the 10th highest salary without using TOP or RowNumber() over partition?
Since this is a stupid nonsensical restriction 😉 , heres mine
Use denali
select * from #SAL
ORDER BY salary ASC
OFFSET 2 ROWS
FETCH NEXT 1 ROWS ONLY

Hi Dave,

as far as I'm concerned, your answer is the best approach so far, since you're using the latest technology. Wait, it's not the latest, it's the next! Even better then 😀

As a replacement of my IDENTITY approach, we could also make use of the SEQUENCE table...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]