This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In the PostgreSQL, You can also calculate RANK of a query using available windows functions.
Like SQL Server, PostgreSQL also provides functions like: RANK(), DENSE_RANK().
I am sharing one demonstration on calculating RANK of students bases on their marks. I require top 3 students result where more than one student has same marks.
Create a table with sample data:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE tbl_Students ( StudID INT ,StudName CHARACTER VARYING ,TotalMark INT ); INSERT INTO tbl_Students VALUES (1,'Anvesh',88),(2,'Neevan',78) ,(3,'Roy',90),(4,'Mahi',88) ,(5,'Maria',81),(6,'Jenny',90); |
Using DENSE_RANK(), Calculate RANK of students:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
;WITH cteStud AS ( SELECT StudName ,Totalmark ,DENSE_RANK() OVER (ORDER BY TotalMark DESC) AS StudRank FROM tbl_Students ) SELECT StudName ,Totalmark ,StudRank FROM cteStud WHERE StudRank <= 3; |
The Result:
|
1 2 3 4 5 6 7 8 |
studname | totalmark | studrank ----------+-----------+---------- Roy | 90 | 1 Jenny | 90 | 1 Anvesh | 88 | 2 Mahi | 88 | 2 Maria | 81 | 3 (5 rows) |
Leave a Reply