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.
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;
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
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";
