Home > Mysql > Find second largest element

Find second largest element


A query to find out nth maximum number in a table.
Let consider a example:

Table name is ‘test’ and this table has two field i.e id and element. Table structure looks like..

————————
| id      | element
————————
|1        | 2
|1        | 4
|1        | 1
|1        | 7
|1        | 5
|1        | 9
————————

Now write a query to find second max element in this table.

SELECT element FROM test ORDER BY element DESC LIMIT 1,1;
above query output is:7

Now common query is:
SELECT element FROM test ORDER BY element DESC  LIMIT ( n – 1 ) , 1 ;
Here n is maximum number, which you want to find.

Advertisements
  1. nknknkn
    November 6, 2012 at 6:20 pm

    select element from test t1 where 2=(select count(distinct(element)) from table t2
    where t2.element>t1.element);

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: