Transact-SQL equivalent of MySQL LIMIT

Transact-SQL equivalent of MySQL LIMIT

• RAM123

  Also, an important note is that your current where clause will only return your variable row number through row 25. To truly emulate the mySQL LIMIT syntax you would need another variable to hold the number of records to include past the start row.

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  CREATE PROCEDURE getMessages

  @username varchar(10),

  @startFrom int,

  @numberOfRows int

  AS

  BEGIN

  DECLARE @End int

  SET @End = @startFrom + @numberOfRows

  SELECT ID,Sender, Subject, Date, New, Unread

  FROM(

  SELECT ID,Sender, Subject, Date, New, Unread, ROW_NUMBER() OVER(ORDER BY ID DESC)[RowNumber]

  FROM Messages

  WHERE Username = @username

  ) TQ

  WHERE RowNumber BETWEEN @startFrom AND @End

  END

  GO

  
  

• Rafael (Live Butterfly)

  Now im confused.

  Can someone please change my stored procedure example to include between 5 and 25 Thanks in advance.

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  -- =============================================

  -- Author: <Author,,Name>

  -- Create date: <Create Date,,>

  -- Description: <Description,,>

  -- =============================================

  CREATE PROCEDURE getMessages

  -- Add the parameters for the stored procedure here

  @username varchar(10),

  AS

  BEGIN

  -- Insert statements for procedure here

  SELECT ID,Sender, Subject, Date

  FROM Messages

  WHERE Username = @username

  ORDER BY ID DESC

  END

  GO

  
  

• laja

  Im using the following code but it complains about near 'Over':

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  CREATE PROCEDURE getMessages

  @username varchar(10),

  @startFrom int

  AS

  BEGIN

  SELECT ID,Sender, Subject, Date, New, Unread

  FROM Messages

  WHERE Username = @username

  ORDER BY ID DESC

  SELECT ID,Sender, Subject, Date, New, Unread

  FROM(

  SELECT ID,Sender, Subject, Date, New, Unread, ROW_NUMBER(OVER ORDER BY ID DESC) [RowNumber]

  FROM Messages

  WHERE UserName = @username

  )

  WHERE RowNumber BETWEEN @startFrom AND 25

  END

  GO

  Anyone have any ideas

  
  

• mamo

  Now i have this but its complaining about line 23 near 'WHERE'. Any ideas

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  CREATE PROCEDURE getMessages

  @username varchar(10),

  @startFrom int

  AS

  BEGIN

  SELECT ID,Sender, Subject, Date, New, Unread

  FROM(

  SELECT ID,Sender, Subject, Date, New, Unread, ROW_NUMBER() OVER(ORDER BY ID DESC)[RowNumber]

  FROM Messages

  WHERE Username = @username

  )

  WHERE RowNumber BETWEEN @startFrom AND 25

  END

  GO

  
  

• Bob Willer

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  -- =============================================

  -- Author: <Author,,Name>

  -- Create date: <Create Date,,>

  -- Description: <Description,,>

  -- =============================================

  CREATE PROCEDURE getMessages

  -- Add the parameters for the stored procedure here

  @username varchar(10),

  AS

  BEGIN

  -- Insert statements for procedure here

  SELECT ID,Sender, Subject, Date

  FROM Messages

  WHERE Username = @username

  ORDER BY ID DESC

  SELECT ID, Sender, Subject, Date

  FROM(

  SELECT ID, Sender, Subject, Date, ROW_NUMBER(OVER ORDER BY ID DESC) [RowNumber]

  FROM Messages

  WHERE UserName = @username

  )T

  WHERE RowNumber BETWEEN 5 AND 25

  END

  GO

  
  

• tr7

  If you are using 2005 you can use the ROW_NUMBER() function within a CTE or derived table and then use the column as your limit.

  Your query would look something like:

  SELECT col1, col2

  FROM(

  select col1, col2, ROW_NUMBER() OVER(ORDER BY col1) [rowNumber]

  from myTable

  )q

  WHERE rowNumber BETWEEN 2 and 25

  Further details in books on line here: http://msdn2.microsoft.com/en-us/library/ms186734.aspx

  
  

• Ofir Epstein

  Thank you, ill try it soon.
  

• dragoncells

  I hit post too early. Meant to add that you can use variables in place of the 5 and 25 to get the proper range you wish.
  

• D13G0

  Works great. Thank you.
  

• Sailu

  You need an alias after the outer select. try this:

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  CREATE PROCEDURE getMessages

  @username varchar(10),

  @startFrom int

  AS

  BEGIN

  SELECT ID,Sender, Subject, Date, New, Unread

  FROM(

  SELECT ID,Sender, Subject, Date, New, Unread, ROW_NUMBER() OVER(ORDER BY ID DESC)[RowNumber]

  FROM Messages

  WHERE Username = @username

  ) TQ

  WHERE RowNumber BETWEEN @startFrom AND 25

  END

  GO

  
  
• Transact-SQL equivalent of MySQL LIMIT