How to Select Top 100 rows in Oracle?
SqlOracleSql Problem Overview
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.
Sql Solutions
Solution 1 - Sql
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)
Solution 2 - Sql
you should use rownum in oracle to do what you seek
where rownum <= 100
see also those answers to help you
Solution 3 - Sql
As Moneer Kamal said, you can do that simply:
SELECT id, client_id FROM order
WHERE rownum <= 100
ORDER BY create_time DESC;
Notice that the ordering is done after getting the 100 row. This might be useful for who does not want ordering.
Update:
To use order by with rownum you have to write something like this:
SELECT * from (SELECT id, client_id FROM order ORDER BY create_time DESC) WHERE rownum <= 100;
Solution 4 - Sql
First 10 customers inserted into db (table customers):
select * from customers where customer_id <=
(select min(customer_id)+10 from customers)
Last 10 customers inserted into db (table customers):
select * from customers where customer_id >=
(select max(customer_id)-10 from customers)
Hope this helps....
Solution 5 - Sql
To select top n rows updated recently
SELECT *
FROM (
SELECT *
FROM table
ORDER BY UpdateDateTime DESC
)
WHERE ROWNUM < 101;
Solution 6 - Sql
Try this:
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) alias_name
WHERE rownum <= 100
ORDER BY rownum;
Or TOP:
SELECT TOP 2 * FROM Customers; //But not supported in Oracle
NOTE: I suppose that your internal query is fine. Please share your output of this.