How to select 5th Max in a Table

ramk_1978 · December 28th, 2004, 12:31 PM

Hi,

I have a question in writing the query.

In a table , two fields namely:
Sno Salary
with lot of records going on.

now, I have to select the 5th Max salary in the above table, how can do this.

Ramkumar

A.D.Ramkumar

jemacc · December 28th, 2004, 02:16 PM

The with ties will include any records that match the last item in the query

Code:

Select TOP 5 WITH TIES *
FROM Table A
Order by salary desc

and it also works with SELECT TOP 5 PERCENT WITH TIES

Jaime E. Maccou

Vadivel · December 30th, 2004, 01:41 AM

Sample Table Structure:
-----------------------

CREATE TABLE [dbo].[UserTable] (
    [RowNumber] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [UserName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Age] [int] NULL
) ON [PRIMARY]
GO

Sample records:
---------------

07,Rajinikanth,56
01,Sachin,31
03,Vadivel,28
11,Sneha,25
12,Reema Sen,19

Query to find the 5th maximum age:
----------------------------------

Select * From
    (
    Select TOP 1 * From
        (
            Select TOP 5 * from UserTable order by age
        )
        as D1 ORDER BY age
    )
    as D2 ORDER BY age

Best Regards
Vadivel

MVP ASP/ASP.NET
http://vadivel.thinkingms.com