How to use Common Table Expression (CTE) In SQL
By using Common Table Expression(CTE) we can create a temporary table and will use ROW_NUMBER() function. Result table after applying ROW_NUMBER() function will be temporary store. It will not reflet in the database. Here we are assuming that no two salary will be the same in same partition.
WITH temp_table AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY "Group_Id" ORDER BY "Salary" DESC) AS rownum
FROM "Test"
)
SELECT "Id", "Name", "Group_Id", "Salary"
FROM temp_table
WHERE rownum = 2;
Output:
|
Id |
Name |
Group_Id |
Salary |
|---|---|---|---|
|
1 |
Yash |
1 |
25000 |
|
5 |
Keval |
2 |
30000 |
|
7 |
Jenil |
3 |
15000 |
Explanation:
Here we have used Common Table Expression(CTE) . Using which we have created temp_table and applied ROW_NUMBER() on the Salary and also done partition on Group_Id. Then we have fetched second row of each partition which will be 2nd highest salary of each Group.
We have created a temporary table using Common Table Expression (CTE). Then we have applied partition over Group_Id and set the salary is descending order. Then using ROW_NUMBER() we have given a sequence to each partition. Since we assume that no two person have same salary in the same partition, 2nd highest salary will have row number 2 in each partition.
List the Second Highest Salary By Department
Listing the second-highest salary by department is a common task that explains the use of advanced PostgreSQL techniques. It offers several functions and methods to achieve this.
In this article, we will explore three approaches that help us to List the Second Highest Salary By Department with the help of examples and so on.
Contact Us