Select records from one record to another in sql server

Asked

Viewed 1,324 times

0

I want to select from record 20 to 50 in SQL SERVER, but I’m not getting it!

I’m wearing it like this: 

SELECT * FROM table OFFSET 20 ROWS FETCH NEXT 50 ROWS ONLY

But it doesn’t work!

I know you have the: Top 10 That will list from line 0 to 10

sql select server

  • Can you put the query you’re using? I know the question may be simple, but if you don’t give us more information it will be difficult to give valid answers.

  • I’m using it like this: SELECT * FROM table OFFSET 20 ROWS FETCH NEXT 50 ROWS ONLY But it doesn’t work! I know you have: TOP 10 which will list from line 0 to 10

  • What is your version of SQL Server?

  • 2008 version of SQL Server

  • 2

    Possible duplicate of Limit and Offset for Web Paging in SQL Server

3 answers

1

The syntax OFFSET/FETCH NEXT is only available from SQL Server 2012. Instead of using it, create a query which will return all your data together with the function ROW_NUMBER which will assign a sequential integer to each row allowing you to select only the records at the desired position:

WITH resultado AS
(SELECT coluna1,
        ...,
        ROW_NUMBER() OVER (ORDER BY coluna1, ...) AS linha
   FROM tabela
  WHERE ...)
SELECT *
  FROM resultado
 WHERE linha >= 20
   AND linha < 50

ROW_NUMBER

Returns the sequential number of a row in a partition of a result set, starting at 1 for the first row of each partition.

  • row would be the column? I edited my question, I think I got confused...it would be from record 10 to table 50.

  • @user3081 line is a generated identifier. It would be the line number

  • I tested with this model in Navicat Sql Server, but it was an error. I changed the table, and removed the columns, because I want to select everything in it.

  • @user3081 you have to do the OVER on top of his id column, without it his query won’t run. Without knowing the structure of your table I can’t help you any more than that

0


To do this just use the ROW_NUMBER (Transact-SQL):

SELECT  *
FROM    (
            SELECT  ROW_NUMBER() OVER(ORDER BY id) AS Linha
                ,   *
            FROM    tabela
        ) X
WHERE   X.Linha BETWEEN 10 AND 50

The column id should be the sort column you want to "create" the sequential line numbering.

  • that line I edited my question, line would be the record, ie select the record from position 10 to 50.

  • And that query I put in returns the records from line 10 through 50. That’s what you want, right? You can always put the structure of your table to have a more correct answer (and real).

0

Good afternoon Buddy try it this way.

USE vendateste
GO

WITH TesteVenda AS
--Nesse Sub Select uso o  ROW_NUMBER Para numerar a saída do conjunto do resultado 
(
SELECT 
        ROW_NUMBER() OVER(ORDER BY IdContrato ASC) AS RowNumber,
        IdContrato, DataBaseContrato    
    FROM Venda 
)
--Aqui trago os dados do meu sub select
SELECT
        IdContrato, 
        DataBaseContrato,
        RowNumber
    FROM TesteVenda
    --Aqui seleciono as linhas que eu quero no meu resultado
    WHERE RowNumber BETWEEN 10 and 20

ROW_NUMBER

Numbers the output of a set of results. More specifically, returns the sequential number of a row in a partition of a result set, starting at 1 in the first row of each partition.

Documentation:ROW_NUMBER (Transact-SQL)

Browser other questions tagged sql select server

You are not signed in. Login or sign up in order to post.