SUNIL GUTTA

SUNIL GUTTA

  • NA
  • 1k
  • 385k

top nth salary but at my need ??

Jan 19 2014 7:24 AM
Hi
This is very basic one . lot of solutions available but my requirement is some what different ..
TABLE top'n'salary
NAME SAL 
hirpshima999.00
sunil1000.00
hirpshima11999.00
pokiri3000.00
pokiri13000.00
pakis5162.00
pakis15162.00
gazani18000.00
gazani8000.00
linus11000.00
linus111000.00
By the look at my table you can see salary columns are duplicated I HAVE NOT CREATED PRIMARY KEY Note that ..
Requirement : I need say 3rd highest salary with NAME .. i.e O/P should be like Pakis 5162
                                                                                                                          pakis1 5162 .. just becoz i have 2 salaries with different names  of 3rd highest type ..

My work :  
   Way 1 : select top 1 t.* from (select distinct top 3 [sal] from [top'n'salary] order by sal desc)as t order by sal ... i was able to get only salary ..
   Way 2 : select MAX(sal),name from [top'n'salary] where sal < (select MAX(sal) from [top'n'salary]) .. here i tested like will this work or not .. SUb query to find                    2nd highest but giving me error like Msg 8120, Level 16, State 1, Line 1
Column 'top'n'salary.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Thank You friends  
 &

Finally possible at end can someone explain me what is this query
select * from( select id,name,row_number() over(order by id) as 'row'  from student)as temp where row=n

Answers (4)