MySQL Query To Get Second Largest Number

By | August 27, 2012

Using SQL query, we can get second largest number from database. By using php code also, we can do this task. Necessity of using SQL query to get second largest number is, server load will be gradually decreased. In php coding. a SQL query and atleast 3 php functions will be used.

 

Here is the database table structure, two columns id and name.

 

Only way to do this task is by using sub query.

1] first we have to select maximum id value from the table. Then we have to select maximum id from other than first maximum id. (i.e 9 is selected as maximum id, then maximum id have to be selected from table other than value 9)

SELECT max(id) FROM tb where id not in (select max(id) from tb )

 

2] A Maximum id value have to be selected from all ids, then another query of selecting maximum id less than actual maximum id. ( 9 is selected as maximum id, then maximum id less than 9 have to selected and that is the second largest number)

SELECT max(id) FROM tb where id<(select max(id) from tb )

One thought on “MySQL Query To Get Second Largest Number

  1. Stephan

    What about ordering by id and taking the second one?

    select distinct id from tb order by id desc limit 1,1

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

freeway-ruddily