vinu512 (8/17/2012)
From what I understand, this should do it:
Declare @position Char(33)
Set @position = 'sales'
Select ename, job From
(
Select *, ROW_NUMBER() Over (Partition By job Order By Sal) As rn From Ex
) As a
Where job = @position AND rn = 3
I hope this is what you are looking for.
Just one note. If we are searching max there should be "Order By Sal desc". I think it's a mistype.
raghuldrag
Consider the case where there will be no more than 2 persons on postion. Should we loose them, or not?
If not, so maybe there is better to use: rn <=3, and than top(1)...order by rn desc.
Also, why are you defining number column like varchar datatype? There might be a problem, because strings sorted in another way than numbers.