Hi, I am having a newbie question here which was asked in an interview: How to find the fifth largest salary from an employee table where the salary column is of numeric type: Select Salary from employee Group By (salary) The above query will give me all distinct salaries. Max() will give me the highest salary. Please reply.

Since you posted this in the 2005 forum section, have a look at ROW_NUMBER(). Quite handy for these kind of problems.

When I've had to solve this, I've found building a view on the base information and then selecting from that worked. In this case, you could build a view on the top 5 salaries ( CREATE VIEW vwTop5 AS SELECT TOP 5 salary FROM salaries ORDER BY salary) and then use the following SQL statement (SELECT Max (salary) FROM vwTop5 ) to get the last element. I haven't found a better way to do it, so maybe someone else can come up with a one-line response. Tim kb0odu

[quote user="kboodu"]In this case, you could build a view on the top 5 salaries ( CREATE VIEW vwTop5 AS SELECT TOP 5 salary FROM salaries ORDER BY salary) and then use the following SQL statement (SELECT Max (salary) FROM vwTop5 ) to get the last element.[/quote] Hi kboodu, I think I should be having order by DESC here Does order by work for numeric column? (I think it works for varchar or Nvarchar but won't return correct values for numeric , int, float........) can someone clarify on this, am I correct.

Order by works for any numeric (smallest to largest for ascending) or date column (earliest to latest). For text, it uses dictionary case order (as specified by your colation). You'll have to look to see where you need min/max. I did this off the top of my head. You may need to use the MIN function to get the smallest amount. Tim

Again, thanks to the new ranking functions available from SQL Server 2005 onwards, these kind of problems have become very easy to solve. If you are only interested in the 5th largest salary you can use something like this: DECLARE @t TABLE (ename varchar(10), salary int) INSERT INTO @t(ename, salary) SELECT 'Bob', 1000 INSERT INTO @t(ename, salary) SELECT 'Tom', 1100 INSERT INTO @t(ename, salary) SELECT 'Dick', 1200 INSERT INTO @t(ename, salary) SELECT 'Harry', 1900 INSERT INTO @t(ename, salary) SELECT 'Peter', 1200 INSERT INTO @t(ename, salary) SELECT 'Paul', 2500 INSERT INTO @t(ename, salary) SELECT 'Mary', 1500 INSERT INTO @t(ename, salary) SELECT 'Dilbert', 5000 SELECT X.salary FROM (SELECT ROW_NUMBER() OVER (ORDER BY T.salary DESC) AS rn, * FROM @t T) X WHERE X.rn = 5 If you are additionally interested in who earns the 5th largest salary somethine like this will work. SELECT * FROM (SELECT DENSE_RANK() OVER (ORDER BY T.salary DESC) AS rn, * FROM @t T) X WHERE X.rn = 5 There is no need for any additional object such as a view in between.

You can also use many methods http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx

[quote user="Madhivanan"] You can also use many methods http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx [/quote] Hi Madhivanan in the second last method ( method 5th) Select min(num) from (select top 5 num from @number order by num desc) T I think it should be ( in case there are repeated numbers) Select min(num) from (select top 5 num from @number group by num order by num desc) T the remaining also have the same problem; I find myself incapable of correcting them at the present level of knowledge I have.

[quote user="shankbond"] [quote user="Madhivanan"] You can also use many methods http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx [/quote] Hi Madhivanan in the second last method ( method 5th) Select min(num) from (select top 5 num from @number order by num desc) T I think it should be ( in case there are repeated numbers) Select min(num) from (select top 5 num from @number group by num order by num desc) T the remaining also have the same problem; I find myself incapable of correcting them at the present level of knowledge I have. [/quote] Yes. You are correct

Hi, You can use row_number/rank/denserank function in SQL Server to get the largest salary http://venkattechnicalblog.blogspot.com/2010/04/finding-n-th-highest-value-in-sql.html Cheers, Venkatesan prabu .J

[quote user="FrankKalis"]There is no need for any additional object such as a view in between.[/quote] Thanks for the solution Frank. Also I am thankful to all of the other people who helped me.