Thea Burger's Blog

If I can remember the ANANSI-92 SQL standard, the standard for limiting return rows is through the usage of Set Rowcount statement:

Set Rowcount 10

I came back to give a more specific example of what I meant:

Set Rowcount 10
Select * from SomeTableThatYouShouldNotAllSelect

Thanks Justin, it may be the standard, but Oracle doesn't support it :(

Is anyone aware of a way of limiting the number of rows in SQL Server by using the where-clause instead of "select top x", similar to oracle?
/Pelle

Thea: Thanks for this posting. I just googled for "oracle select top equivalent" and your page was the first in the list.
Mike

Like Mike L I was searching for a way to do this in Oracle. Thanks!

Hey man that code is great! i know MySQL and SQL Server.. but forced to use oracle for college work and well the tutors coudlnt even give me the answers that you have on this page and there getting paid for it!

actually the code for Oracle only works if you do not ORDER BY. let's say you have lots of records and you want to sort them by "product" and getting just the top 10 records. You can't do it by just:
"SELECT product, descr, email
FROM products
WHERE ROWNUM <= 10
ORDER BY product"
the result is not what you wanted.. which is the scenario i'm facing now..
what "ROWNUM <= 10" does is simply return you top 10 record in the table.. if you try the ORDER BY code, it will simply extract the top 10 records in the table then ORDER it instead of ORDERING them then select the top 10...

Use in Oracle
SELECT MAX (column_name) FROM table_name
for the top 1 or
SELECT MAX (column_name) FROM table_name WHERE ROWNUM<=10
Good luck!
Gabinux

For sorting problems you may create a View the select Top 10 (rownum<=10)

Take a look here!

http://www.ispirer.com/doc/sqlways38/Output/SQLWays-1-044.html

_________________ doesnt work _________________
SELECT product, descr, email
FROM products
WHERE ROWNUM <= 10
ORDER BY product"
__________________ has to be this way: ________

SELECT product, descr, email
FROM (select product, descr, email from products order by product )
WHERE ROWNUM <= 10

SELECT product, descr, email
FROM products
WHERE ROWNUM <= 10 # the data is not ordered yet
ORDER BY product"

Rank should give you the equivalent of Top.

I wanted to see the status of the max record, grouped by the job_num


select * from (select
JOB_NUM,
JOB_NME,
START_DT_GMT,
STATUS_CD,
rank() over(partition by JOB_NUM order by START_DT_GMT desc
) rn
from NM_SCHD_JOB_RUN
)
where rn = 1

bwood, you're a lifesaver. Hadn't run into that oracle rank function before. Had always had to do top n in oracle the long way.

Thanks!!!

GO

Thanks for the rank() tip, it helped me too :-)

The rank function is indeed a nice one to simulate TOP behaviour. But take care, because the numbers rank return are not always as consecutive as the "ROWNUM in a subquery"solution. Rownum will always return consecutive numbers like 1, 2, 3, ... X for the first X records. Rank() OVER (order by col1 ASC) however could e.g. return 1, 1, 3 as ranks for the first three records if the first two records if the first two records have the same sort order value for col1. In that case, there is no nr 2. As long as you use it on unique cols, no such problem off course.

I’m trying to limit my query, I have 4,000,000 of rows and I’m trying to get the results by sets
1st set 0-500
2nd set 501-1001
3rd set 1002-1502
Etc…

Any help would be appreciated,

Thanks.

João,
I had the same problem, and solved it by using CEIL(rownum/size of set). You could for instance have a separate set_id column, do an update

update some_table set set_id = ceil(rownum/500)

and then simply use set_id to choose a specific set

Hi all,
I am too converting a project of mine from Access to Oracle. I want an equivalent query in Oracle....
SELECT TOP 1 * From MTable
where NumericWP > 0
Order By((23-Latitude)*( 23-Latitude)+(73-Longitude) * (73-Longitude))

thanks! this blog was so helpful.
thanks a lot from japan!

A select query returns 20 rows
I need to select rows from 10 to 20 . how i can do it in oracle and in mysql
somebody suggest me something

devendra:

Can't help with the MySQL, but in Oracle, you can just use the ROWNUM:

SELECT fld
FROM tbl
WHERE ROWNUM >= 10
AND ROWNUM <= 20

I need select n tuples at time,
but I need do that "in pure" SQL-92 with out facilities of
oracle and MySQL somebody can help me ?

Thank you from Poland ;)

devendra,
in MySQL you can:
SELECT * from foo where something = 'somethingelese' limit 10, 20

thanks alot.

For ORACLE, the following will work for extracting first N rows or even rows between n and n, etc.

It is important to make sure your do not use rownum, as rownum is actually the physical row number from the table.  This means that asking for rownum <= 100 means you are basically asking for the first 100 records physically added to the table - these same rows will always be rownum 1 through 100, regardless of sort order. This means that sorting on rownum will always yield 1, 2, 3, 4, ... through 100, whereas sorting on any other expression will yield something like 7, 20, 3, 1, 4, ... 87.

So, in order to get what I think you are looking for, you must use the row_number() function along with a window specification (advanced stuff this is!).  The following is a working example...

SELECT
id,
name,
iRow
FROM
(
SELECT
id,
name,
row_number() over (order by id) AS iRow
from
SomeTable
)
WHERE
iRow <= 100

The row_number() function returns a CALCULATED row number based on the window specification which in the above example consists of the 'over (order by id)' portion.  With a little tweaking of the window specification, we can do some pretty cool stuff.  

As an example, let's say we wanted only the first two rows from each set of rows having the same 'name' (i.e., if there are 5 rows with name=Smith and 10 rows with name=Meyers, I want only 4 rows in the result set, 2 from each name.  To accomplish this, you would change the window specification to the following...

over (partition by name order by id)

basically, the partition by clause tells row_number() when to increment (like saying increment within this grouping).  The result will be that you will get sequential numbering by the partition by specification.  i.e...

Smith    1
Smith    2
Smith    3
Smith    4
Meyers  1
Meyers  2
Meyers  3
etc.  

And amazingly, changing the order by will not effect this numbering within the partition.  Changing the sort order will yield the results in the sorted order, but the row_number() will still be correct.  Like this...

Smith    1
Smith    4
Meyers  1
Smith    3
Meyers  2
Smith    2
Meyers  3

You can still see the third Meyers or second Smith regardless of sort order!!  POWERFUL STUFF THIS!

You can definitely use this for finding duplicates in any field simply by changing the partition by clause.

Hope this was helpful,

Rodney Barbati
rpbarbati@hotmail.com

I WANT TO DISPLAY THE RECORD NUMBER IN MYSQL QUERY. i.e. SAME AS BELOW QUERY:

SELECT ROWNUM, EMPNAME FROM EMPLOYEES;

IN ORACLE ITS FINE. BUT IN MYSQL HOW TO GET RECORD NUMBER ?

ANY BODY HELP ME ??

THANKS

I WANT TO DISPLAY THE RECORD NUMBER IN MYSQL QUERY. i.e. SAME AS BELOW QUERY:

SELECT ROWNUM, EMPNAME FROM EMPLOYEES;

IN ORACLE ITS FINE. BUT IN MYSQL HOW TO GET RECORD NUMBER ?

ANY BODY HELP ME ?? REPLY : bhavin1979@hotmail.com

THANKS

I WANT TO DISPLAY THE RECORD NUMBER IN MYSQL QUERY. i.e. SAME AS BELOW QUERY:

SELECT ROWNUM, EMPNAME FROM EMPLOYEES;

IN ORACLE ITS FINE. BUT IN MYSQL HOW TO GET RECORD NUMBER ?

ANY BODY HELP ME ?? REPLY : bhavin1979@hotmail.com / bhavinsparikh@yahoo.com

THANKS

Valeeeeeeu. ( This is tks in pt-BR ).
=D

You save my life! :)

Thank's.

Great stuff, second on the list in google, but by far the easiest.  I was looking for a way to do this in Oracle and bang - it's done.  Nice one.

Can anyone help convert the following into the oracle equivalent please.

SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 90 * FROM (SELECT TOP 100 PERCENT * FROM customers ORDER BY companyname) AS x) AS t1 ORDER BY companyname  DESC) AS t2 ORDER BY companyname

Hi, can anybody help convert the following to an oracle model?

SELECT * FROM (SELECT TOP 10 * FROM (SELECT TOP 10 * FROM (SELECT TOP 100 PERCENT * FROM customers ORDER BY companyname) AS x) AS t1 ORDER BY companyname  DESC) AS t2 ORDER BY companyname

Here is a couple of things ive done so far, im trying to build a new query based around products.

SELECT *
FROM (SELECT DISTINCT product_code, product_category, order_date,
ROW_NUMBER () OVER (ORDER BY product_code)
AS roworder
FROM backorder_detail
WHERE product_code != 'DEL')
WHERE roworder BETWEEN 0 AND 10

and the percentage bits I have managed to do is

SELECT *
FROM (SELECT *
FROM backorder_detail
ORDER BY 1)
WHERE ROWNUM <= 100 * (SELECT COUNT (*)
FROM backorder_detail) / 100
ORDER BY product_code

Just struggling to combine the two.

how can i get top 10-20 records from sql server table

how to select ten by ten records in sql plesase help me .........

Hi Thea, I was wondering why Im not gtn lucky, but ur post really helped me. Thanks man. -CHirag

THANK YOU FOR THIS! I've been scouring for proper syntax and you are the ONLY one to have it posted...that I could find. Thanks again.

This is really what I want. I don't need to sort the output, I just want to select 2 records which match the where clause. And also I need to support microsoft SQL 2005 and oracle. This article is great!

Thanks a lot!!! This is really I am looking for to select from Oracle client and MS SQL 2005 client

Thank you.This is exactly what I was looking for, information is crisp and clear

The blog is very useful! Thanks very much from China!

Awesome Thea, nice blog you have here to solve little annoying problems not found on "main" Oracle sites... Thanks from Saturn, Gilinex

it so fustrating to seach something very ..."simple"??