CODEWITHSUNDEEP
mysqlsqlsql-serveroracle-database
Night Walker
Night Walker

Reputation: 21270

select the TOP N rows from a table

I am making some paging, and I need to make some query and get the result form defined slicing . for example: I need to get all "top" rows in range 20n < x < 40n etc.

SELECT * FROM Reflow  
WHERE ReflowProcessID = somenumber
ORDER BY ID DESC;

and now I need to make my sliding by column called ID .

Any suggestions how to so ? I need to run my query on mysql, mssql, and oracle.

Upvotes: 44

Views: 190467

Answers (5)

Gabriele Franco
Gabriele Franco

Reputation: 899

From SQL Server 2012 you can use a native pagination in order to have semplicity and best performance:

https://learn.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-ver15#using-offset-and-fetch-to-limit-the-rows-returned

Your query become:

SELECT * FROM Reflow  
WHERE ReflowProcessID = somenumber
ORDER BY ID DESC;
OFFSET 20 ROWS  
FETCH NEXT 20 ROWS ONLY;

Upvotes: 0

user11853432
user11853432

Reputation: 1

you can also check this link

SELECT * FROM master_question WHERE 1 ORDER BY question_id ASC LIMIT 20

for more detail click here

Upvotes: 0

Pravin Bansal
Pravin Bansal

Reputation: 4681

select * from table_name LIMIT 100

remember this only works with MYSQL

Upvotes: 7

Bassam Mehanni
Bassam Mehanni

Reputation: 14944

Assuming your page size is 20 record, and you wanna get page number 2, here is how you would do it:

SQL Server, Oracle:

SELECT *   -- <-- pick any columns here from your table, if you wanna exclude the RowNumber
FROM (SELECT ROW_NUMBER OVER(ORDER BY ID DESC) RowNumber, * 
      FROM Reflow  
      WHERE ReflowProcessID = somenumber) t
WHERE RowNumber >= 20 AND RowNumber <= 40

MySQL:

SELECT * 
FROM Reflow  
WHERE ReflowProcessID = somenumber
ORDER BY ID DESC
LIMIT 20 OFFSET 20

Upvotes: 66

Eric
Eric

Reputation: 97591

In MySql, you can get 10 rows starting from row 20 using:

SELECT * FROM Reflow  
WHERE ReflowProcessID = somenumber
ORDER BY ID DESC
LIMIT 10 OFFSET 20 --Equivalent to LIMIT 20, 10

Upvotes: 28

Related Questions