Nth maximum record in Sybase
How do I find the nth maximum in Sybase?
This is another variation on the "Top Ten SQL" problem. A previous answer, Top 5 salaried employees, not using TOP, gave the SQL to find the top 5 salaries in each department --
select * from employee X where 5 > (select count(*) from employee where dept = X.dept and salary > X.salary) order by dept, salary desc
This syntax is perfectly okay for Sybase, as it uses no proprietary keyword like TOP or LIMIT.
To find the nth overall, the department column is not needed and the subselect must find n-1 rows with a higher value --
select * from yourTable X where n-1 = (select count(*) from yourTable where salary > X.salary) order by salary desc
Note that it has to be the nth maximum something -- the above example finds the nth highest salary.