mysql limit equivalent

mysql limit equivalent

• Tony128

  That's not exactly the same.  This is from the mysql docs:
  
  

  The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be non-negative integer constants (except when using prepared statements).

  With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

  mysql> SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15
  
  
  
   The rows returned in the example should be 
  select *
  from #t
  where seq between 6 and 25
  
  
  
  
  
  
  
  
  
  

  
  
  

• KirillA

  on the sql server 2k syntax, if an identity is already part of the return columns in t, then you cannot introduct another identity into the table.

  How do you work it in that case then

  
  

• akka

  This is crazy. LIMIT has been so useful and powerful in Oracle and MySQL, why will Microsoft not add it to their standard, or at least change TOP to handle the same functionality.

  LIMIT type statements have made 500% improvements in my web apps. I have emulated in MSSQL, but it doesn't always work and when doing JOINs and ORDER BY's it really gets nasty.

  Please tell me why Microsoft won't just see the light on this simple clause and add it ! ! !

  
  

• firstfruits

  We have received lot of feedback on making server-side paging syntax easier and considering it for a future version of SQL Server.

  As for ROW_NUMBER, why do you say that it doesn't work when doing JOINs or ORDER BY The ORDER BY of the outermost SELECT affects only the order in which the rows are returned to the client. ROW_NUMBER() has it's own ORDER BY clause so you can effectively number rows in different order and it doesn't impact the presentation order. With ROW_NUMBER you are just counting rows based on set of column(s) and optional ordering so joins doesn't really matter either. Can you give some specific examples where you have problems converting LIMIT syntax to SQL Server

  And the effect of paging results is often misunderstood. Generating a row number without ordering the rows in the result can produce unpredictable behavior. You can end up retrieving some rows in say page #1 in subsequent pages depending on how the engine executes the query and so on. So application requirements is a key consideration before implementing paging. The use of ROW_NUMBER forces you to think about these issues carefully. Note that Oracle also implements the same and it is effective replacement for ROWNUM pseudo-column.

  
  
  

• jeremymarx

  In that case, you need to do something like below:

  select t.identitycol + 0 as identitycol, identity(int, 1) as seq, ....

  into #t

  from tbl as t

  order by ....

  By using an expression with identity column in the existing table that doesn't affect the value, the identity property will not be persisted in the table when you do SELECT... INTO.

  
  
  

• force_fx

  Greetings.
  
  select * from (select *, ROW_NUMBER() OVER(ORDER BY t.column1) as seq
  from test as t) as t1 where t1.seq between 24*25 and 25*25
  
  Is this best practice for "get page 25 with pagesize 25"
  
  http://search.msn.com/results.aspx q=bill&first=625
  http://searchpalette.com/spalette/24/25/bill
  
  

• aleph

  There is no equivalent syntax. The closest you can get to in SQL Server 2000 is to do the following:
  
  select t.*, identity(int, 1, 1) as seq
  into #t
  from test as t
  order by t.column1
  
  select *
  from #t
  where seq between 5 and 20
  
  In SQL Server 2005, you can do the same using ROW_NUMBER() function.
  
  select *
  from (
  select *, ROW_NUMBER() OVER(ORDER BY t.column1) as seq
  from test as t
  ) as t1
  where t1.seq between 5 and 20
  
  
  
  

• subdigital

  Note that while ROW_NUMBER() is not strictly in the ANSI standard, the basic approach for sequence or ranking functions is and other engines implement this.  So, it's likely to become fairly broadly adopted.
  
  Conor Cunningham
  SQL Server Query Optimization Development Lead
  
• mysql limit equivalent