Get Nth Highest Value by SQL Query


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 bymarks asc
GO 
with im as (select *,dense_rank() over(order by marks desc) as rn  fromtblDupRecord)
select * from im where rn=2




Reference from :http://www.c-sharpcorner.com/Blogs/10295/query-to-get-nth-highest-value.aspx

Comments

Post a Comment

Popular posts from this blog

Find Store Procedures and Functions in MS SQL Server.

Hi Friends , This article will help you to find Store Procedures and Functions in Microsoft SQL Server. Execute this Script which i mentioned below. ROUTINE_TYPE =  'PROCEDURE' OR    'Function' Select  ROUTINE_TYPE , SPECIFIC_NAME , ROU TINE_DEFINITION FROM     INFORMATION_SCHEMA.ROUTINES WHERE    OBJECTPROPERTY ( OBJECT_ID ( SPECI FIC_NAME ), 'IsMSShipped' )   = 0         AND  ROUTINE_DEFINITION   like   '%PrcName%'         AND  ROUTINE_TYPE = 'PROCEDURE'
Read more

Find procedure and function in SQL Server

ROUTINE_TYPE =   'PROCEDURE' OR     'Function' Select   ROUTINE_TYPE , SPECIFIC_NAME , ROU TINE_DEFINITION FROM      INFORMATION_SCHEMA.ROUTINES WHERE     OBJECTPROPERTY ( OBJECT_ID ( SPECI FIC_NAME ), 'IsMSShipped' )   = 0          AND   ROUTINE_DEFINITION    like   '%prcleave%'          AND   ROUTINE_TYPE = 'PROCEDURE'
Read more

Windows Service Install and Un Install in MS VisualStudio 2010

Hi Friends, You can learn How to Install and Un Install Windows Service using Microsoft Technology. >> 1 .First get the path where InstallUtil.exe exist its Mostly the path is same but differ version  it can be differ C:\Windows\Microsoft.NET\Framework\v4.0.30319\InstallUtil.exe >> 2 .Get the Path where you want to put that Service .exe file (Assume it is on "E:\MyApp\MyTestApp.exe") >> 3 .Command for Install service Run Visual Studio Command Prompt as Run admin if Windows 8 else no need to it and Type this C:\Windows\Microsoft.NET\Framework\v4.0.30319\InstallUtil.exe E:\MyApp\MyTestApp.exe -i >> 4 .Command for Un Install service Run Visual Studio Command Prompt as Run admin if Windows 8 else no need to it and Type this C:\Windows\Microsoft.NET\Framework\v4.0.30319\InstallUtil.exe E:\MyApp\MyTestApp.exe -u >> 5 .In case if you already installed Service and you can see there "services.msc" Service list and you have ...
Read more