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
Join the conversation! Your thoughts help the community grow.