CODEWITHSUNDEEP
mysqlsql
Punit Naik
Punit Naik

Reputation: 515

Get second highest values from a table

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.

Upvotes: 1

Views: 95

Answers (3)

Denys Séguret
Denys Séguret

Reputation: 382150

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;

Upvotes: 2

SIDU
SIDU

Reputation: 2278

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

Upvotes: 0

Biswabid
Biswabid

Reputation: 1411

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

Upvotes: 0

Related Questions