Get second highest values from a table

Question

I have a table like this:

+----+---------+------------+
| id | conn_id | read_date  |
+----+---------+------------+
|  1 |       1 | 2010-02-21 |
|  2 |       1 | 2011-02-21 |
|  3 |       2 | 2011-02-21 |
|  4 |       2 | 2013-02-21 |
|  5 |       2 | 2014-02-21 |
+----+---------+------------+

I want the second highest read_date for particular 'conn_id's i.e. I want a group by on conn_id. Please help me figure this out.

Answer 1

Here's a solution for a particular conn_id :

select max (read_date) from my_table
where conn_id=1
and read_date<(
   select max (read_date) from my_table
   where conn_id=1
)

If you want to get it for all conn_id using group by, do this:

select t.conn_id, (select max(i.read_date) from my_table i
where i.conn_id=t.conn_id and i.read_date<max(t.read_date))
from my_table t group by conn_id;

Answer 2

Following answer should work in MSSQL :

select id,conn_id,read_date  from (
select *,ROW_NUMBER() over(Partition by conn_id order by read_date desc) as RN 
from my_table
)
where RN =2

There is an intresting article on use of rank functions in MySQL here :
ROW_NUMBER() in MySQL

Answer 3

If your table design as ID - date matching (ie a big id always a big date), you can group by id, otherwise do the following:

$sql_max = '(select conn_id, max(read_date) max_date from tab group by 1) as tab_max';

$sql_max2 = "(select tab.conn_id,max(tab.read_date) max_date2 from tab, $sql_max
where tab.conn_id = tab_max.conn_id and tab.read_date < tab_max.max_date 
group by 1) as tab_max2";

$sql = "select tab.* from tab, $sql_max2 
where tab.conn_id = tab_max2.conn_id and tab.read_date = tab_max2.max_date2";