sql select * between exact number of rows

Question

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

Answer 1

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

Answer 2

This can be done very easily in SQL Server 2012. By using the new feature of OFFSET and FETCH. This will help you to pull out desired rows in an already ordered/sorted result set.

Please see the below example:

SELECT
             PP.FirstName + ' ' + PP.LastName AS 'Name'
            ,PA.City
            ,PA.PostalCode
FROM  Person.Address PA
            INNER JOIN
                        Person.BusinessEntityAddress PBEA
                                    ON PA.AddressID = PBEA.AddressID 
            INNER JOIN
                       Person.Person PP
                                    ON PBEA.BusinessEntityID = PP.BusinessEntityID
ORDER BY PP.FirstName
            OFFSET 0 ROWS
                FETCH NEXT 5 ROWS ONLY

Please notice the OFFSET 0 and FETCH NEXT 5 written above.
This is will display only 5 rows starting from 0 the row.

Answer 3

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.

Answer 4

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.

Answer 5

In SQL Server,

select * from tablename order by columnname offset 20 rows fetch next 40 rows only

It treats 21st row as 1st row and fetches next 40 rows from a 21st row.

Answer 6

We can do this by multiple way.

1. we can do with the help of offset-fetch clause.

   select * from Table_Name order by Column_Name offset 234 rows fetch next 16 rows only

it will fetch the record between 235-250. because it will skip first 234 rows and will fetch next 16 rows.

2. we can use simple select statement with where clause.

   Select * from Table_Name where Column_Name Between 235 and 250

it will also fetch same result.

Hope it will help.

Answer 7

If you're using Microsoft SQL (2005>) you can use the ROW_NUMBER function

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;

Answer 8

Following worked from me in oracle

select * from (select rownum serial,sp.* from sample_table st) sam 
where sam.serial > 10 and sam.serial <= 20;