SQL Server Select top 10-20 results?

Question

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?

Answer 1

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

Answer 2

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?

Answer 3

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

Answer 4

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

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

Answer 5

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

Answer 6

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 

Answer 7

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

Answer 8

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 

Answer 9

Use the code:

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

Answer 10

SELECT * FROM Product LIMIT(10,10)

Answer 11

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 

Answer 12

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

Answer 13

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

Answer 14

Try this:

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

Answer 15

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.

Answer 16

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;