How to Select Top 100 rows in Oracle?
oracle, sql
Solution
Assuming that create_time contains the time the order was created, and you want the 100 clients with the latest orders, you can:
- add the create_time in your innermost query
- order the results of your outer query by the `create_time desc`
- add an outermost query that filters the first 100 rows using `ROWNUM`
Query:
SELECT * FROM (
SELECT * FROM (
SELECT
id,
client_id,
create_time,
ROW_NUMBER() OVER(PARTITION BY client_id ORDER BY create_time DESC) rn
FROM order
)
WHERE rn=1
ORDER BY create_time desc
) WHERE rownum <= 100
UPDATE for Oracle 12c
With release 12.1, Oracle introduced "real" Top-N queries. Using the new `FETCH FIRST...` syntax, you can also use:
SELECT * FROM (
SELECT
id,
client_id,
create_time,
ROW_NUMBER() OVER(PARTITION BY client_id ORDER BY create_time DESC) rn
FROM order
)
WHERE rn = 1
ORDER BY create_time desc
FETCH FIRST 100 ROWS ONLY)
Problem
My requirement is to get each client's latest order, and then get top 100 records. I wrote one query as below to get latest orders for each client. Internal query works fine. But I don't know how to get first 100 based on the results. ``` SELECT * FROM ( SELECT id, client_id, ROW_NUMBER() OVER(PARTITION BY client_id ORDER BY create_time DESC) rn FROM order ) WHERE rn=1 ``` Any ideas? Thanks.