Sql Server & SSRS Tips

Syntax: SELECT names          , salary          ,row_number () OVER (ORDER BY salary DESC) as ROW_NUMBER          ,rank () OVER (ORDER BY salary DESC) as RANK          ,dense_rank () OVER (ORDER BY salary DESC) as DENSE_RANK  FROM salaries Output: Row_number assign different number to them. Rank and Dense_rank both assign same rank to A and B. But interesting thing is what RANK and DENSE_RANK assign to next row? Rank assign 5 to the next row, while dense_rank assign 4. The numbers returned by the DENSE_RANK function do not have gaps and always have consecutive ranks.  The RANK function does not always return consecutive integers.  The ORDER BY clause determines the sequence in which the rows are assigned their unique ROW_NUMBER within a specified partition.   These all can be used ...

One way is tou use subquery like this: select Min(Salary) from salary where salary in ( select distinct top 3 salary from salary order by salary desc) ********************************************* Other way is to use dense rank this: select top 1 * from ( select salary,rn=dense_rank() over (order by salary desc) from salary ) as a where a.rn=3 These queries are for third highest salary, if you want your desired highest salary then replace TOP 3 with TOP (desired number) from above query. And Replace Where a.rn=3 with Where a.rn=(Desired number) from 2nd query.