Get the second highest value in a MySQL table

by

Here’s one that accounts for ties.

Name    Salary
Jim       6
Foo       5
Bar       5
Steve     4

SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees))

Result --> Bar 5, Foo 5

EDIT:
I took Manoj’s second post, tweaked it, and made it a little more human readable. To me n-1 is not intuitive; however, using the value I want, 2=2nd, 3=3rd, etc. is.

/* looking for 2nd highest salary -- notice the '=2' */
SELECT name,salary FROM employees
WHERE salary = (SELECT DISTINCT(salary) FROM employees as e1
WHERE (SELECT COUNT(DISTINCT(salary))=2 FROM employees as e2
WHERE e1.salary <= e2.salary)) ORDER BY name

Result --> Bar 5, Foo 5

More Related Contents:

  1. mysql error cant fix the error [duplicate]
  2. Why can’t this entry be added because of Foreign Key error?
  3. MySQL pivot row into dynamic number of columns
  4. How should I resolve –secure-file-priv in MySQL?
  5. node-mysql multiple statements in one query
  6. MySQL server startup error ‘The server quit without updating PID file’
  7. Best type of indexing when there is LIKE clause [duplicate]
  8. Difference between Key, Primary Key, Unique Key and Index in MySQL
  9. What’s the difference between MyISAM and InnoDB? [duplicate]
  10. How can I access the MySQL command line with XAMPP for Windows?
  11. When to use STRAIGHT_JOIN with MySQL
  12. Allow all remote connections, MySQL
  13. Import CSV to MySQL
  14. Get most recent row for given ID
  15. MySQL Install: ERROR: Failed to build gem native extension
  16. Is merge statement available in MySQL
  17. Mysql covering vs composite vs column index
  18. MYSQL syntax not evaluating not equal to in presence of NULL
  19. How do I populate a MySQL table with many random numbers?
  20. MySQL loop through tables
  21. INSERT INTO or UPDATE with two conditions
  22. How to change the default charset of a MySQL table?
  23. What the difference between mysql and mysql2 gem
  24. How to fix MySql: index column size too large (Laravel migrate)
  25. mysql custom sort
  26. MYSql config bind-address set to 0.0.0.0 but netstat shows different on Ubuntu
  27. How do I alter a mysql table column defaults?
  28. MySQL Recursive get all child from parent
  29. is it possible to call a sql script from a stored procedure in another sql script?
  30. How do I extract Month and Year in a MySQL date and compare them?

Leave a Comment