Does Presto SQL really lack TOP X functionality in SELECT statements?
If so, is there a workaround in the meantime?
Answer
If you simply want to limit the number of rows in the result set, you can use LIMIT, with or without ORDER BY:
SELECT department, salary
FROM employees
ORDER BY salary DESC
LIMIT 10
If you want the top values per group, you can use the standard SQL row_number() window function. For example, to get the top 3 employees per department by salary:
SELECT department, salary
FROM (
SELECT department, salary row_number() OVER (
PARTITION BY department
ORDER BY salary DESC) AS rn
FROM employees
)
WHERE rn <= 3