CREATE TABLE [dbo].[tblDupRecord](
[id] [int] NULL,
[name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[marks] [int] NULL
) ON [PRIMARY]
GO
insert into [tblDupRecord]
values(1,'Name1',25)
insert into [tblDupRecord]
values(2,'Name2',50)
insert into [tblDupRecord]
values(3,'Name3',35)
insert into [tblDupRecord]
values(4,'Name4',40)
insert into [tblDupRecord]
values(5,'Name5',45)
insert into [tblDupRecord]
values(6,'Name6',45)
GO
select * from tblDupRecord a
where 2= (select count( distinct(b.marks)) from tblDupRecord b where
a.marks <= b.marks)
Go
select top 1 with ties * from (
select top 2 with ties * from
tblDupRecord order by
marks desc) as t order by marks asc
GO
with im as (select *,dense_rank() over(order by marks desc) as rn from
tblDupRecord)
select
* from im where rn=2