Lintcode -1918 · Height of the second tallest player

• Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

describe

Create a SQL statement to get the second highest height of players

Table definition: Players

**

The column name of the output is second_height

The sample

A sample:

Table content: Players

As in the players table above, the SQL query should return 200 as the second highest height. If there is no second highest height, the query should return NULL

Example 2:

Table content: Players

As in the players table above, SQL queries should return NULL

Answer key

Method 1

First, find the maximum height, and then, based on the maximum height, find the second highest value.

Using the Max function, first find the maximum value, then pick out all numbers less than the maximum value, and again use Max to find the second largest value.

select max(height) as second_height from players
where height < (select max(p1.height) from players p1);

Copy the code

This uses not in (! =) find the other height values, and then, among the remaining height values, find the second largest height value.Copy the code

select max(height) second_height from players where height not in (select max(height) from players );

Copy the code

Method 2

This solution is mainly a pagination idea, first sort by height, then use limit and offset to find the second one.

select (select distinct height second_height from players
order by height desc 
limit 1 
offset 1) as second_height 
Copy the code

Solution 3

The IFNULL() function is used to determine whether the first expression is NULL and returns the value of the second argument if it is NULL and the value of the first argument if it is not NULL.

The syntax for IFNULL() is:

Select ifnull((select distinct height from players order by height desc limit 1,1),null) as second_height;Copy the code

Copy the code