Dense Function with CTE
Dense function is nothing but a Dense_Rank() function is used to give the same rank to the rows if it contains the same value in result set. We will replace the Rank() from the approach 1.
WITH total_count AS (
SELECT COUNT(*) AS cnt
FROM employees
),
ranked_rows AS (
SELECT *,
DENSE_RANK() OVER (ORDER BY employee_id) AS dense_rank
FROM employees
)
SELECT *
FROM ranked_rows, total_count
WHERE dense_rank > (cnt * 3.0 / 4.0)
Output:
Explanation: Dense_rank is nothing but the rank given to the row and cnt will be the total count which is coming from the total_count CTE.
List the Last 25% Rows in a Result Set in PostgreSQL
In PostgreSQL, extracting specific portions of a result set can be achieved using a variety of SQL techniques. One common requirement is to retrieve the last 25% of rows from a result set. This can be useful for various purposes, such as paginating results, performing analyses on a subset of data, or optimizing queries for performance.