CODEWITHSUNDEEP
sqlsql-server
Nicklas
Nicklas

Reputation: 293

SQL Server Select top 10-20 results?

I have two columns the first one I want top 10 products (1-10)

That is 

SELECT TOP 10 * FROM Product

In the second column I want the next 10 results (11-20)

How do I do that?

Upvotes: 29

Views: 122280

Answers (16)

Shahid Malik
Shahid Malik

Reputation: 171

This my be the most specific:

SELECT Col1, Col2, Col3  FROM
(SELECT ROW_NUMBER() OVER (ORDER BY Id) AS MyRowNumber, * FROM table) tbl
WHERE MyRowNumber BETWEEN 11 AND 20;

Upvotes: -1

Saad Ahmed
Saad Ahmed

Reputation: 23

You can perform that by using the following SQL query:

SELECT * FROM [table] LIMIT 10 OFFSET 10

This will select rows from my table, it will give me 10 rows, and will skip the first 10 rows.

Upvotes: 0

Access Denied
Access Denied

Reputation: 9501

Starting with SQL server 2012 order by offset/fetch feature:

SELECT *
FROM SomeTable
ORDER BY someIdField 
    OFFSET 10 ROWS
    FETCH NEXT 10 ROWS ONLY;

Upvotes: 5

David Champion
David Champion

Reputation: 11

Use the code:

select top 20 * from [table Name] 
except
select top 10 * from [table Name]

Upvotes: 1

Masum
Masum

Reputation: 119

Try this:

SELECT * FROM
  (SELECT ROW_NUMBER() OVER (ORDER BY ColumnName) AS MyRowNumber, *
  FROM TableName) tbl
WHERE MyRowNumber BETWEEN 20 AND 30

Upvotes: 0

Bharath
Bharath

Reputation: 1

select *
from (select *
      from <table>
      order by id asc)
where rownum <= 20

minus
select *
from (select *
      from <table>
      order by id asc)
where rownum <= 10

Upvotes: 0

gaffcz
gaffcz

Reputation: 3479

SELECT p.* FROM 
(
 SELECT *, ROW_NUMBER() OVER (ORDER BY /*some column*/) AS row_num
 FROM Product
) AS p
WHERE p.row_num BETWEEN 11 AND 20

Upvotes: 0

Nabmeister
Nabmeister

Reputation: 795

I would do this:

SELECT [columns] FROM [table names] ORDER BY [column name] DESC LIMIT 10 OFFSET 10;

This is simpler and less complex....

What do you guys think?

Upvotes: 14

Xiquinho F da Silva
Xiquinho F da Silva

Reputation: 21

WITH result_set AS 
    (SELECT ROW_NUMBER() OVER 
        (ORDER BY Product.ID DESC) AS 
     [row_number], Product.intId AS id, Product.Title As Title
     FROM Product WHERE Product.Price > 11)
SELECT * FROM result_set WHERE [row_number] BETWEEN 10 AND 19

Upvotes: 1

senthilkumar2185
senthilkumar2185

Reputation: 2578

select top 10 wwwhid from wwwh  where wwwhid not in(select top 10 wwwhid from wwwh)

Upvotes: 9

R.Ghanbari
R.Ghanbari

Reputation: 29

declare @FromRange int
declare @ToRange int
set @FromRange =11
set @ToRange =20
SELECT top(@ToRange-@FromRange+1) * FROM [tbl] 
where tbl_id not in (select top (@FromRange-1) tbl_id from tbl)
ORDER BY tbl_id

Upvotes: 2

niktrs
niktrs

Reputation: 10056

I am not sure this is the best way to do it but it works

select *
from
(
SELECT top 10 ROW_NUMBER() OVER(ORDER BY product) linenum, product
FROM products
) t1
 JOIN 
(
SELECT top 20 ROW_NUMBER() OVER(ORDER BY product) linenum, product
FROM products
) t2 ON t1.linenum+10 = t2.linenum

Upvotes: 2

marc_s
marc_s

Reputation: 755471

In SQL Server, it's bit tricky to get this done. If you're on SQL Server 2005 or newer, you can use a CTE with a CROSS JOIN and some trickery to get the result you're looking for:

;WITH TopProducts AS
(
    SELECT 
        ProductID, ProductName,
        ROW_NUMBER() OVER(ORDER BY --some-column-here-- DESC) 'RN'
    FROM dbo.Products
)
SELECT 
    p1.ProductID, p1.ProductName,
    p2.ProductID, p2.ProductName
FROM 
    TopProducts p1
CROSS JOIN 
    TopProducts p2
WHERE 
    p1.RN BETWEEN 1 AND 10        -- get rows 1-10 from the first CTE
    -- AND p2.RN BETWEEN 11 AND 20   redundant, as niktrs pointed out
    AND p1.RN + 10 = p2.RN        -- join rows from P1 and P2 so you don't get a cartesian product

The CTE (Common Table Expression) numbers the products - this needs to be based on some column from your Products table, but you didn't mention what column defines the order.

We then select rows 1-10 from the CTE, as well as rows 11-20 from a second instance of the CTE. If you leave this as is, you'll get 100 rows - each combination of rows 1-10 from the first result set against each of the 10 rows from the second result set.

That's why you need an additional condition, based on the row numbers, to "join" one row from each result set, and thus you'll get ten rows - the first column has the items 1-10 from the Products table, the second column has rows 11-20

Upvotes: 4

Martin Smith
Martin Smith

Reputation: 453910

WITH T AS
(
SELECT TOP 20 name, 
       row_number() OVER (ORDER BY id) AS RN
FROM Products
ORDER BY id
)
SELECT 
       MAX(CASE WHEN RN <=10 THEN name END) AS Col1,
       MAX(CASE WHEN RN > 10 THEN name END) AS Col2
FROM T       
GROUP BY RN % 10

Upvotes: 32

user929948
user929948

Reputation:

Try this one : 

SELECT * 
FROM 
( 
    SELECT TOP 10 ROW_NUMBER() OVER(ORDER BY ColName) RowNo, ColName
    FROM TableName 
) table1 
INNER JOIN  
( 
    SELECT TOP 20 ROW_NUMBER() OVER(ORDER BY ColName) RowNo, ColName 
    FROM TableName 
) table2 ON table1.RowNo + 10 = table2.RowNo

Upvotes: 1

tonekk
tonekk

Reputation: 478

SELECT * FROM Product LIMIT(10,10)

Upvotes: 0

Related Questions