Q
Problem solve Get help with specific problems with your technologies, process and projects.

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.

This was last published in February 2002

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close