取到排序后(从大到小)第二个数据
在一个排序后的表中如何取到第二个数据，如下：
tb(col1 char(2),col2 int)
col1 col2
a1 15
a1 14
a1 13
a1 11
a1 9
a2 22
a2 20
a2 10
a2 5
...
想通过sql得到
col1 col2
a1 14
a2 20
...
如果想得到第三.....请问如何得到？
[解决办法]
select col1,col2
from
(
select col1,col2,
tmp_id=(select count(*) from tb where col1=a.col1 and col2> =a.col2)
from tb a
) T
where tmp_id=2
[解决办法]
create table tb(col1 char(2),col2 int)
insert into tb
select 'a1 ',15
union all select 'a1 ',14
union all select 'a1 ',13
union all select 'a1 ',11
union all select 'a1 ', 9
union all select 'a2 ', 22
union all select 'a2 ', 20
union all select 'a2 ', 10
union all select 'a2 ', 5
select * from tb a
where col2 in (select top 1 col2 from (select top 2 col2 from tb b where a.col1=b.col1 order by col2 desc)t order by col2)
/*
col1 col2
---- -----------
a1 14
a2 20
（所影响的行数为 2 行）
*/
[解决办法]
select * from tb a
where col2 in (select top 1 col2 from (select distinct top 2 col2 from tb b where a.col1=b.col1 order by col2 desc)t order by col2)
