Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
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
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

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[^]
 
Share this answer
 
v2
Comments
kannanvdsr89 26-May-14 3:19am    
but i need, which is possible?
SQL
select * from (select num,name,sal,dense_rank()over(order by sal desc) rank from #ime) result
where result.rank=2
 
Share this answer
 
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
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
 
Share this answer
 
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
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
 
Share this answer
 
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.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900