Let’s create a table for demonstration and insert some records into the table.

–Create a test table

CREATE TABLE Tbl_Partition_Test

(

TestName VARCHAR(10),

Topic VARCHAR(15),

[Year] INT,

Total INT

)

–Insert records in above table

INSERT INTO Tbl_Partition_Test VALUES(‘SQLEXE’,‘SQL’,2005,10)

INSERT INTO Tbl_Partition_Test VALUES(‘SQLEXE’,‘SQL’,2006,17)

INSERT INTO Tbl_Partition_Test VALUES(‘SQLEXE’,‘SQL’,2007,124)

INSERT INTO Tbl_Partition_Test VALUES(‘SQLEXE’,‘SQL’,2008,124)

INSERT INTO Tbl_Partition_Test VALUES(‘SQLEXE’,‘.NET’,2008,24)

INSERT INTO Tbl_Partition_Test VALUES(‘SQLRANK’,‘SQL’,2007,14)

INSERT INTO Tbl_Partition_Test VALUES(‘SQLRANK’,‘.NET’,2007,18)

INSERT INTO Tbl_Partition_Test VALUES(‘SQLRANK’,‘SQL’,2008,14)

After created above records, let’s think usability of ROW_NUMBER() and PARTITION BY clause in our real world. Following is easy solution for the same as sub-query or/and Group By clause.

First example, we will use ROW_NUMBER() and PARTITION BY then we will filter out record set. Partition is like group by, what we list sorted base on “TestName” so we will putting it in Partition by clause and Order by clause in below query will give Serial Number with grouping 1 to highest.

–E.g.#1 Adding Sno with partition by a column name

SELECT TestName,Topic,[Year],Total,

ROW_NUMBER() OVER (PARTITION BY TestName ORDER BY TestName) AS SNoGroup

FROM Tbl_Partition_Test

 

Second example, let’s use RANK() and PARTITION BY clause and then will filter record set. The following result list sorted based on “TestName” so we will be putting it in Partition by clause and Order by clause in below query will give Ranking 1 to highest “Total”.

–E.g.#2 Adding ‘Ranking’ with partition by a coloum name

SELECT TestName,Topic,[Year],Total,

RANK() OVER (PARTITION BY TestName ORDER BY Total DESC) AS ‘Ranking’

FROM Tbl_Partition_Test

Third example, we will select highest Total from the result ranking with Partition by clause. Because we could not put “Where” condition in above query so that we will amend the query as below.

–E.g.#3 Selecting highest Total ‘Ranking’ with partition

SELECT * FROM (

             SELECT TestName,Topic,[Year],Total,

             RANK() OVER (PARTITION BY TestName ORDER BY Total DESC) AS ‘Ranking’

             FROM Tbl_Partition_Test

             ) Testing

WHERE Ranking < 2