how to solve this or finding second maximum without subquery

Question

1.00/5 (1 vote)

See more:

SQL

create table #ime(num varchar(20),name varchar(20),sal int);
insert into #ime values('1','kannan',10000);
insert into #ime values('2','dinesh',2000);
insert into #ime values('3','dinesh1',3000);

for the above table using that following query i got that column name kk was not found
select name, dense_Rank() over(order by sal)as kk from #ime where kk=2
any buddy help me to identify the second salary from that table without sub-query

Posted 23-May-14 1:20am

kannanvdsr89

Add a Solution

Comments

Prasad Avunoori 23-May-14 7:34am

Hi Kannan,
Find the solution below. If you have any doubt come back.

kannanvdsr89 26-May-14 3:21am

which is using sub query i need with out subquery

DamithSL 23-May-14 7:50am

what is your database server? and the version?

kannanvdsr89 26-May-14 3:20am

sql server 2008 and 10

4 solutions

Solution 4

SQL

select * from 
(
select *, row_number() over(order by sal desc) as rn from table name
) as sub
where rn=2

you can write any number in "where" clause to find nth salary

Posted 23-May-14 2:04am

Awadhendra Tripathi

Updated 23-May-14 2:09am

v4

Comments

Sanjay K. Gupta 23-May-14 8:08am

Please correct the syntax.

kannanvdsr89 26-May-14 3:16am

u r right and its also subquery

Maciej Los 23-May-14 8:09am

Wrong answer (voted 1).
1) Row_number returns wrong result in case of same value is multiplied
Check it and find out why

DECLARE @ime table(num varchar(20),name varchar(20),sal int);
INSERT INTO @ime values('1','kannan',10000),('2','dinesh',2000),('3','dinesh1',3000),('4','dinesh2',3000)

SELECT name, sal, DENSE_RANK() OVER(ORDER BY sal) AS DR, ROW_NUMBER() OVER(ORDER BY sal) AS RN
FROM @ime

2) OP wants to not use subquery.
Please see my answer.

Sanjay K. Gupta 3-Jul-20 7:26am

His answer did not contains sub query, it was derived table. Please update your vote.

Solution 3

As far as i know, it can't be done without subquery (derived table), because it's impossible to use result of ranking functions as a condition.
The only way to achieve that is to use subquery (derived table)!

Official meaning of subquery and derived table: The difference between subqueries and derived tables in SQL[^]

Posted 23-May-14 1:54am

Maciej Los

Updated 23-May-14 2:02am

v2

Comments

kannanvdsr89 26-May-14 3:19am

but i need, which is possible?

Solution 2

Make a subquery as following way

select * from ( select name, dense_Rank() over(order by sal)as kk from #ime) as a
 where a.kk=2

Posted 23-May-14 1:36am

sankarsan parida

Comments

ArunRajendra 23-May-14 7:39am

Its clearly mentioned not to use sub query

Maciej Los 23-May-14 7:58am

Good job (+5), but not enough description.
Please see, my answer.

[no name] 23-May-14 8:10am

thank you very much for appreciation

kannanvdsr89 26-May-14 3:16am

its using sub query

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Prasad Avunoori · Accepted Answer · 2014-05-23T01:32:00

Solution 1

SQL

select * from (select num,name,sal,dense_rank()over(order by sal desc) rank from #ime) result
where result.rank=2

Posted 23-May-14 1:32am

Prasad Avunoori

Comments

ArunRajendra 23-May-14 7:39am

Its clearly mentioned not to use sub query

Prasad Avunoori 23-May-14 7:45am

For your information, That's not a subquery. It's a derived Table.

ArunRajendra 27-May-14 2:11am

I guess you got answer from Maciej and that was the boarder (kannanvdsr89) has mentioned.

Maciej Los 23-May-14 7:58am

Good job (+5), but not enough description. In some cases statements: 'subquery' and 'derived table' have the same meaning.
Please see, my answer.

Animesh Datta 23-May-14 8:05am

my 5

kannanvdsr89 26-May-14 3:18am

i known about that but i need without sub query...?