16

Does Presto SQL really lack TOP X functionality in SELECT statements?

If so, is there a workaround in the meantime?

https://prestodb.io/

David Phillips
  • 10,723
  • 6
  • 41
  • 54
Phillip Deneka
  • 221
  • 1
  • 3
  • 12
  • Do you mean ORDER BY with LIMIT? – Dain Sundstrom Jun 06 '16 at 23:26
  • This is actually two different things. – Phillip Deneka Jun 07 '16 at 06:02
  • Most of the time, TOP N functions the same as ORDER BY with LIMIT, but I am working within an edge case where it doesn't. Oh well. :/ – Phillip Deneka Jun 07 '16 at 06:16
  • Can you describe what the TOP X function does, or what you are attempting to accomplish? – Dain Sundstrom Jun 08 '16 at 16:57
  • I recommend you take the time to test the difference between TOP and LIMIT instead. This link is a good starting point, but testing the two will provide more analysis. http://stackoverflow.com/questions/5668540/difference-between-top-and-limit-keyword-in-sql – Phillip Deneka Jun 15 '16 at 21:09
  • 3
    According to that post and all linked documentation in that post, TOP and LIMIT are the same. What specifically were you attempting to do? – Dain Sundstrom Jun 17 '16 at 00:21
  • @DainSundstrom something like `select mode() within group (order by X) from abc` probably? That's valid Postgres and it's simplifying life a lot when dealing with nested sub-sub-sub-queries, compared to the solution posted as accepted answer – Jivan Apr 11 '19 at 22:50

1 Answers1

25

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
David Phillips
  • 10,723
  • 6
  • 41
  • 54
  • For everyone else, this is as close as we get in Presto.Yeah, this is what I wound up doing. Using TOP N makes it the query both faster and easier to read. I was dealing with large volume of data and trying to avoid memory limits. Ran the code multiple times for subsets of the data and then aggregated it back together. Thank you! – Phillip Deneka Jun 15 '16 at 21:07
  • Ew. It ate my pretty pretty formatting. Haha! Thanks again, David! – Phillip Deneka Jun 15 '16 at 21:07
  • Thanks so much, helped me a bunch! It took me a bit to realize there was a missing comma between 'salary' and 'row_number()' in the third line of that second example. – JDenman6 Aug 29 '23 at 20:54