Return n number of rows from SQL Server query

Question

Possible Duplicate:
Row Offset in SQL Server

I have a query which has a like condition and it returns around 1500 rows. Now I want to know how can I get the rows part by part, e.g. return 75 rows, then again the next 75 rows until it reaches the end of the rows. I know I can use something like SELECT TOP 75

I tried something like this but it does not return any rows

    SELECT * 
    FROM 
       (SELECT ROW_NUMBER() OVER (ORDER BY WarehouseSubType.id) as row,
               WarehouseSubType.id, 
               WarehouseType.name as WarehouseTypename,
               WarehouseType.alternateName AS WarehouseTypealternateName,
               WarehouseSubType.name AS WarehouseSubTypename,
               Warehouse.alternateName AS WarehousealternateName,
               WarehouseSubType.alternateName AS WarehouseSubTypealternateName,
               WarehouseSubType1.name AS WarehouseSubType1name,
               WarehouseSubType1.alternateName AS WarehouseSubType1alternateName,
               Warehouse.alternateName AS Warehousename,
               Branch.name AS Branchname,
               Branch.alternateName AS BranchalternateName,
               WarehouseProductQuantity.actualQuantity,
               WarehouseProductQuantity.reservedQuantity,
               Supplier.companyName, 
               Supplier.companyNameAlternate,
               Tafsil.description,
               Tafsil.alternateDescription,
                (WarehouseProductQuantity.actualQuantity - WarehouseProductQuantity.reservedQuantity) AS quantity 
        FROM WarehouseSubType 
        INNER JOIN WarehouseType ON (WarehouseSubType.warehouseTypeId = WarehouseType.id)
        INNER JOIN WarehouseSubType1 ON (WarehouseSubType.id = WarehouseSubType1.warehouseSubTypeId)
   ) a 
WHERE 
    warehouseTypename like '%Ve%' 
    AND row > 0 and row < 75

See this answer: http://stackoverflow.com/questions/187998/row-offset-in-sql-server — Shawn Steward, Oct 18 '12 at 16:22
@ShawnSteward infact i have used that but does not return any records — ZAJ, Oct 18 '12 at 16:29
What version of SQL? If 2012 then there are paging operators — Pete Carter, Oct 18 '12 at 16:38
@ZAJ: if it doesn't return any records, then there isn't any record for your condition... — techBeginner, Oct 18 '12 at 16:49

Pete Carter · Accepted Answer · 2012-10-18T17:25:52.543

The code you posted should not even compile, let alone return rows. The sub query is not closed and row would not be recognised in the inner query.

Try this instead:

SELECT * FROM 
   ( SELECT ROW_NUMBER() OVER (ORDER BY WarehouseSubType.id) as row,
    WarehouseSubType.id, 
    WarehouseType.name as WarehouseTypename,
    WarehouseType.alternateName AS WarehouseTypealternateName,
    WarehouseSubType.name AS WarehouseSubTypename,
    Warehouse.alternateName AS WarehousealternateName,
    WarehouseSubType.alternateName AS WarehouseSubTypealternateName,
    WarehouseSubType1.name AS WarehouseSubType1name,
    WarehouseSubType1.alternateName AS WarehouseSubType1alternateName,
    Warehouse.alternateName AS Warehousename,
    Branch.name AS Branchname,
    Branch.alternateName AS BranchalternateName,
    WarehouseProductQuantity.actualQuantity,
    WarehouseProductQuantity.reservedQuantity,
    Supplier.companyName, 
    Supplier.companyNameAlternate,
    Tafsil.description,
    Tafsil.alternateDescription,
    (WarehouseProductQuantity.actualQuantity - WarehouseProductQuantity.reservedQuantity) AS quantity 
    FROM WarehouseSubType 
    INNER JOIN WarehouseType 
        ON ( WarehouseSubType.warehouseTypeId = WarehouseType.id)
    INNER JOIN WarehouseSubType1 
        ON (WarehouseSubType.id = WarehouseSubType1.warehouseSubTypeId)) a 
    WHERE warehouseTypename like '%Ve%' ) b
WHERE b.row > 0 and b.row< 75

Return n number of rows from SQL Server query

1 Answers1