Thread: DB2 query

How to retrieve the third maximum value from a column ?
For Ex: If i have emp-id as one column with values 111,458,785,896,274,698 etc, using SELECT MAX(EMP-ID) retrieves the value "896". But i need to retrieve third maximum value ie., 698 incase of above example. How to write query for this ?

Query to select top nth record:

consider the table name as emp,then

select empid
from emp e1
where ( n =
( select count ( distinct ( e2.empid ) )
from emp e2
where e2.empid >=e1.empid))

replace n by 3 to get the third maximum

select max(empid) from table1
where empid not in (select distinct(empid) from table1
order by empid desc
fetch first 2 rows only)