Subquery with Row Number
Now we will try to make the above query with subquery rather than CTE for that we will replace the CTE with subquery while all the other steps would be same.
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (ORDER BY employee_id) AS row_num,
(SELECT COUNT(*) FROM employees) AS cnt
FROM employees
) sub
WHERE row_num > (cnt * 3.0 / 4.0)
Output:
Explanation: For above output we have taken two subquery from which the innermost query returns the cnt column which contains the total rows. While the row_num is coming from the second Subquery.
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.
Contact Us