Friday 20 July 2018

How to find nth maximum salary records from a table?


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[member]') AND type in (N'U'))
DROP TABLE [dbo].[member]
GO

CREATE TABLE [dbo].[member](
[member_no] [int] IDENTITY(1,1) NOT NULL,
[lastname] [varchar](50) NOT NULL,
[firstname] [varchar](50) NULL,
[salary] [decimal](10,2) NULL
)
GO   


ALTER TABLE [member] ADD CONSTRAINT [PK_member_lastname] PRIMARY KEY  -- NONCLUSTERED
(
          [lastname] ASC
         
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF,  
          ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

SET IDENTITY_INSERT [dbo].[member] ON
INSERT [member] ([member_no], [lastname], [firstname], [Salary])
  VALUES  (1, 'Krishnan', 'Kumar', 10000.00),
 (2, 'Raman', 'Chandrarn', 9000.00),
          (3, 'Durga', 'Prasad',  11000.00),
          (4, 'Saratha', 'Devi',  8000.00),
          (5, 'Shivan', 'Anbu',  9000.00),
          (6, 'Govindaraj', 'Kanniappan', 12000.00),
          (7, 'Parvathi', 'Kumari', 11000.00),
          (8, 'Easwari', 'Sundari', 9500.00)
                  
SET IDENTITY_INSERT [dbo].[member] OFF


-- 5th Maximum salary records
SELECT a.*
FROM member a WITH (NOLOCK) WHERE
5 =(SELECT COUNT(DISTINCT salary)
FROM member b WITH (NOLOCK) WHERE b.salary >= a.salary)

OUTPUT:

member_no
lastname
firstname
salary
2
Raman
Chandrarn
9000.00
5
Shivan
Anbu
9000.00



No comments:

Post a Comment