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