RE: Oracle "limit"
- From: Rodney Haynie <RodneyH@xxxxxxxxxx>
- To: "programmingblind@xxxxxxxxxxxxx" <programmingblind@xxxxxxxxxxxxx>
- Date: Tue, 14 Apr 2009 08:36:13 -0400
Good docs, and I bet the answer is the same that goes on in SQL Server.
SQL Server does not have the LIMIT clause like MySQL.
So you have a two step process.
In SQL Server 2005 you can make use of the new ROW_NUMBER() function:
Select * from
(
SELECT ROW_NUMBER() OVER (ORDER BY fieldName) AS [Row],
Field2, field3, etc...
FROM tableName
)
WHERE Row BETWEEN 11 and 20
So you'll notice this is doing a subquery. Reason is because you have to
process the row numbers first.
HTH.
-Rodney
Please consider the environment before printing this email.
-----Original Message-----
From: programmingblind-bounce@xxxxxxxxxxxxx
[mailto:programmingblind-bounce@xxxxxxxxxxxxx] On Behalf Of Peter Quaiattini
Sent: Monday, April 13, 2009 1:37 PM
To: programmingblind@xxxxxxxxxxxxx
Subject: RE: Oracle "limit"
Hi Octavian
The reason your query with the where clause of
rownum<=20 and rownum>=10
returns no rows is as folllows.
The value for ROWNUM is assigned by Oracle as follows (taken directly from the
section entitled 'ROWNUM Pseudocolumn' in the SQL Reference manual):
" For each row returned by a query, the ROWNUM pseudocolumn returns a number
indicating the order in which Oracle selects the row from a table or set of
joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so
on."
The value of ROWNUM is the number of the row retrieved initially out of the
database and does not represent the order in which rows are displayed (although
you can force this thru the use of a subquery). So don't count on ROWNUM
returning the same row for multiple executions of the same query -- there is no
guarantee of this.
Further, the manual says the following, which describes why your query returns
no rows:
"Conditions testing for ROWNUM values greater than a positive integer are
always false. For example, this query returns no rows:
SELECT * FROM employees WHERE ROWNUM > 1;
The first row fetched is assigned a ROWNUM of 1 and makes the condition false.
The second row to be fetched is now the first row and is also assigned a ROWNUM
of 1 and makes the condition false. All rows subsequently fail to satisfy the
condition, so no rows are returned."
My question to you is why you need rows between 10 and 20?
Why can you not simply select the first 10 rows?
Peter Quaiattini
BITS OPS - Data Hosting Services
Canadian Pacific Railway
(403) 319-6579
peter_quaiattini@xxxxxx
-----Original Message-----
From: =?utf-8?B?T2N0YXZpYW4gUsOixZ9uacWjxIM=?= <orasnita@xxxxxxxxx>
Subject: Oracle "limit"
Date: Sat, 11 Apr 2009 18:17:22 +0300
Hi,
Does anyone know Oracle's syntax for the MySQL's equivalent "limit" clause?
I've seen that the following SQL query works:
select column_name from schema_name.table_name where rownum<=20;
But it doesn't return anything if I use:
select column_name from schema_name.table_name where rownum<=20 and
rownum>=10;
...or other combinations.
And I want to select from the row 10 to the row 20.
Thanks.
Octavian
__________
View the list's information and change your settings at
//www.freelists.org/list/programmingblind
__________
View the list's information and change your settings at
//www.freelists.org/list/programmingblind
Other related posts:
