Hi all,
I would like to know if I could using select statement retrieve exact position of the rows. e.g rows between 235 & 250. Is this possible?
Thanks in advance, shashi
+1
A:
If your using mySQL you could use the limit command for example:
SELECT * FROM TableName LIMIT 235, 15
Where the first number is the start index and the second is the number of rows to return.
Malachi
2010-09-03 12:38:32
Am I going daft, or does >= 235 and <= 250 still mean 16 rows, not 15?
Marjan Venema
2010-09-03 13:48:17
16 rows isn't it? as it's inclusive of both boundries
Malachi
2010-09-03 15:39:19
+3
A:
I don't know of a general way.. but each DB has a way. For example in oracle you can do it with a nested select
Oracle:
select * from (
select a, b, c from table_foo
where id = 124
)
WHERE rownum >= 235
and ROWNUM <= 250
MSSQL
select * from
(select Row_Number() over
(order by userID) as RowIndex, * from users) as Sub
Where Sub.RowIndex >= 235 and Sub.RowIndex <= 250
MySQL
SELECT * FROM TableName LIMIT 235, 15
bwawok
2010-09-03 12:39:33
Am I going daft, or does >= 235 and <= 250 still mean 16 rows, not 15?
Marjan Venema
2010-09-03 13:47:49
The below code doesn't work, the while loop doesn't dispaly any values. If I change it to **0** to 150 it works fine. Please help me out here. Anything other than **0** doesn't retrieve any value. ResultSet rset1 = stmt.executeQuery(" SELECT * FROM (SELECT * FROM iris ) WHERE rownum BETWEEN 10 and 150"); while(rset1.next()) { System.out.println(rset1.getString(1)); }
2010-09-05 18:16:49
A:
No, that database is set not a sequence, this mean that You the don't have any specific order.
But when specify the order than everything is much simpler.
Oracle
SELECT * FROM ( SELECT * FROM TABLE ORDER BY COLUMN ) WHERE rownum BETWEEN 235 and 250
In this case You have to use rownum
rownum is a pseudo column. It numbers the records in a result set. The first record that meets the where criteria in a select statement is given rownum=1, and every subsequent record meeting that same criteria increases rownum.
MS SQL
WITH OrderedRecords AS
(
SELECT ColumnA,
ROW_NUMBER() OVER (ORDER BY ColumnA) AS 'RowNumber'
FROM Sales.SalesOrderHeader
)
SELECT * FROM OrderedRecords WHERE RowNumber BETWEEN 235 and 250
GO
For this You have to specify You own order column
For MySQL i don't know how the engine deal with this.
Vash
2010-09-03 12:40:02
Mainly specify the order but You probably refer to how it works; it depend of the db engine, but generally we can say that return and cursor that retrieve the data in specific order, that why in oracle we have to use another select, and in MS SQL we need to specify for our won an row now that oder the set first. This is always "used" last by the database (that why we can use there aliases).
Vash
2010-09-03 12:53:19
A:
If you're using Microsoft SQL (2005>) you can use the ROW_NUMBER fuction
USE AdventureWorks;
GO
WITH OrderedOrders AS
(
SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
FROM Sales.SalesOrderHeader
)
SELECT *
FROM OrderedOrders
WHERE RowNumber BETWEEN 50 AND 60;
SomeMiscGuy
2010-09-03 12:41:25