Finding Nth highest number in SQL Server

Many times you face this question while working with real time data and most interviews. In this post we will go through different ways to find the records with Nth highest number. Use this script to populate some sample data to test the results.

CREATE TABLE PLAYER
(
PLAYERID INT IDENTITY(100,2) NOT NULL,
NAME VARCHAR(100) NOT NULL,
DOB DATETIME,
COUNTRY VARCHAR(3),
PRIZEMONEY MONEY NOT NULL
)
GO

INSERT INTO PLAYER(NAME,DOB,COUNTRY,PRIZEMONEY) VALUES 
('Andre Agassi','4/29/1970','USA',$31152975),  ('Rafael Nadal','6/3/1986','ESP',$27224163), ('Boris Becker','11/22/1967','GER',$25080956),  
('Yevgeny Kafelnikov','2/18/1974','RUS',$23883797), ('Ivan Lendl','3/7/1960','USA',$21262417),  ('Stefan Edberg','1/19/1966','SWE',$20630941),
('Goran Ivanisevic','9/13/1971','CRO',$19876579),  ('Michael Chang','2/22/1972','USA',$19145632), ('Lleyton Hewitt','2/24/1981','AUS',$18312036), 
('Andy Roddick','8/30/1982','USA',$17109084), ('Novak Djokovic','5/22/1987','SRB',$15984098), ('Gustavo Kuerten','9/10/1976','BRA',$14807000),
('Jonas Bjorkman','3/23/1972','SWE',$14600323),  ('Marat Safin','1/27/1980','RUS',$14373291), ('Jim Courier','8/17/1970','USA',$14034132),  
('Carlos Moya','8/27/1976','ESP',$13382822), ('Nikolay Davydenko','6/2/1981','RUS',$1339499),  ('Michael Stich','10/18/1968','GER',$129528),
('Juan Carlos Ferrero','2/12/1980','ESP',$12588898),  ('John McEnroe','2/16/1959','USA',$12552132), ('Thomas Muster','10/2/1967','AUT',$12225910),  
('Tim Henman','9/6/1974','GBR',$11635542), ('Sergio Bruguera','1/16/1971','ESP',$11632199),  ('Patrick Rafter','12/28/1972','AUS',$11127058),
('Thomas Enqvist','3/13/1974','SWE',$10461641),  ('Petr Korda','1/23/1968','CZE',$10448900), ('Alex Corretja','4/11/1974','ESP',$10411354),  
('Todd Woodbridge','4/2/1971','AUS',$10095245), ('Richard Krajicek','12/6/1971','NED',$10077425), ('Roger Federer','8/8/1981','SUI',$53362068),  
('Pete Sampras','8/12/1971','USA',$43280489)
GO

Here is 4 different ways to find the same result.

--Define Level of N
DECLARE @N INT = 3

--Method-1: Without using any functions
SELECT NAME,DOB,COUNTRY,PRIZEMONEY FROM PLAYER WHERE PRIZEMONEY =
(SELECT DISTINCT PRIZEMONEY FROM PLAYER p1 WHERE (@N-1)=
(SELECT COUNT(DISTINCT p2.PRIZEMONEY) FROM PLAYER p2 WHERE p2.PRIZEMONEY > p1.PRIZEMONEY ))

--Method-2: Using TOP
SELECT NAME,DOB,COUNTRY,PRIZEMONEY FROM PLAYER WHERE PRIZEMONEY =
(SELECT TOP 1 PRIZEMONEY FROM PLAYER
WHERE PRIZEMONEY in (SELECT DISTINCT TOP (@N) PRIZEMONEY FROM PLAYER ORDER BY PRIZEMONEY DESC)
ORDER BY PRIZEMONEY)

--Method-3: Using MIN() function
SELECT NAME,DOB,COUNTRY,PRIZEMONEY FROM PLAYER WHERE PRIZEMONEY =
(SELECT MIN([t].[amt]) FROM
(SELECT DISTINCT TOP (@N) PRIZEMONEY AS [amt] FROM PLAYER ORDER BY PRIZEMONEY DESC) AS [t])

--Method-4: Using Dense_Rank()
;WITH cte
AS (
SELECT NAME,DOB,COUNTRY,PRIZEMONEY,  DENSE_RANK() OVER (ORDER BY PRIZEMONEY DESC) AS [rnk]
FROM PLAYER t
)
SELECT NAME,DOB,COUNTRY,PRIZEMONEY
FROM cte
WHERE [rnk] = @N

Also let me know your suggestions on this.

Insert or Copy Data into Table

This post is for a common situation most of us face while working with a database, which is copy/insert to a table.  We can use INSERT & SELECT to copy data into a database table in three ways.

• INSERT Statement (Direct insert data values into the table):

Using INSERT statement we can add one data row at a time to destination table.

--Example: 1
CREATE TABLE Table01
(Column1 INT PRIMARY KEY, Column2 varchar(10))
GO

INSERT INTO Table01(Column1,Column2) VALUES(101,'A')
INSERT INTO Table01(Column1,Column2) VALUES(102,'B')
INSERT INTO Table01(Column1,Column2) VALUES(103,'C')
INSERT INTO Table01(Column1) VALUES(104)
GO

SELECT Column1,Column2 FROM Table01
GO

• INSERT Statement with a SELECT Sub-Query:

If the destination table (we want to populate) is already created in the database, then using INSERT statement with a SELECT sub-query, we can copy data rows from one table to another.  The number of columns listed in INSERT statement must be same as the columns listed in SELECT statement and their data types must match.

--Example: 2
CREATE TABLE Table02
(Column1 INT IDENTITY, Column2 VARCHAR(10),
Column3 VARCHAR(8) DEFAULT('Welcome1'))
GO

INSERT INTO Table02(Column2)
SELECT Column2 FROM Table01
GO

SELECT Column1, Column2,Column3 FROM Table02
GO

• SELECT with INTO Statement:

If the destination table (we want to populate) is not available in the database, then using SELECT INTO statement also it will create the table on the fly and copy data to it from the source table.

--Example: 3
SELECT Column1, Column2, Column3
INTO NewTable
FROM Table02
WHERE Column2 IS NOT NULL
GO

SELECT * FROM NewTable
GO

Please let me know your valuable comments.

Insert Data into table with two columns, one is Identity column and another is RowVersion

This post is from a discussion in one of our SQL Server training session with Mr. Rakesh Mishra.  While discussing RowVersion data type, I have created a table with two columns one is of INT IDENTITY and another is of ROWVERSION type. Now the situations arises is how to insert data into that table?

Note:  ROWVERSION which is auto generated, unique binary number within a database and generally used for version stamping in table rows.

In the ROWVERSION column we can’t explicitly insert data, so can we insert data into the INDENTITY column without making the IDENTITY_INSERT to ON?

Yes, we can do that and here is the way it can be done.

--Create the table for demo
CREATE TABLE RowVersionTest(myID INT IDENTITY PRIMARY KEY, myRowVer ROWVERSION)

What if I will insert data into the IDENTITY column?

--Insert Data
INSERT INTO RowVersionTest(myID) VALUES (1)

MESSAGE:
Msg 544, Level 16, State 1, Line 3
Cannot insert explicit value for identity column in table 'RowVersionTest' when 
IDENTITY_INSERT is set to OFF.

The above error message is because, we have not we can’t insert data into a IDENTITY column till IDENTITY_INSERT is OFF.  So here is the solution for this:

--Insert Data
INSERT INTO RowVersionTest DEFAULT VALUES
INSERT INTO RowVersionTest DEFAULT VALUES
INSERT INTO RowVersionTest DEFAULT VALUES
INSERT INTO RowVersionTest DEFAULT VALUES
INSERT INTO RowVersionTest DEFAULT VALUES
--Select Data
SELECT * FROM #RowVersionTest

--Cleanup

DROP TABLE #RowVersionTest

Please leave your valuable comments.