How to Select Top 100 Rows in Oracle?
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...
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.
6 Answers
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)
you should use rownum in oracle to do what you seek
where rownum <= 100
see also those answers to help you
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;
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....
To select top n rows updated recently
SELECT *
FROM (
SELECT *
FROM table
ORDER BY UpdateDateTime DESC
)
WHERE ROWNUM < 101;
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.
Sarah Jenkins is a veteran tech journalist with over 12 years of experience covering artificial intelligence, mobile innovations, and digital ethics. Her insights have appeared in leading technology publications worldwide.
