How to Select Top 100 rows in Oracle?
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....
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
limit in oracle
select top in oracle
select top in oracle 2
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;