Applying a 'LIMIT' like option to a written SQL Query

Question

So I've been working with this :

SELECT DISTINCT l.* 
  FROM StreamView l
  JOIN Friendships f ON f.Sender = @UserName OR f.Recipient = @UserName 
 WHERE l.Sender <> @UserName 
   AND l.Recipient <> @UserName 
   AND (   l.Sender = f.Recipient 
        OR l.Sender = f.Sender 
        OR l.Recipient = f.Sender 
        OR l.Recipient = f.Recipient) 
ORDER BY DateTime DESC

It works great for getting a list of required records. Anyhow I would like to be able to give this query two new attributes, from which row to start getting records from and a second integer that defines the length.

The reason for this is that I am applying 'lazy scrolling' effect on this data.

I would like to add these two values into the query: (Int32 startAt, Int32 howMany)

Any ideas? Thanks.

Check out [this answer](http://stackoverflow.com/questions/548475/efficient-way-to-implement-paging) — Sergey Kalinichenko, Jan 03 '12 at 03:45
this has been covered so many times ... http://stackoverflow.com/questions/216673/emulate-mysql-limit-clause-in-microsoft-sql-server-2000 .. denali gives you the nicest syntax, but it is not out there — Sam Saffron, Jan 03 '12 at 03:46
http://stackoverflow.com/questions/8088514/can-i-use-limit-n-m-on-sql-server-database — Sam Saffron, Jan 03 '12 at 03:47
http://stackoverflow.com/questions/4358253/sql-server-2008-paging-methods — Sam Saffron, Jan 03 '12 at 03:47

competent_tech · Accepted Answer · 2012-01-03T04:08:40.270

Here's an almost complete solution:

DECLARE 
  @startat INT ,
  @howmany INT

SET @startat = 5
SET @howmany = 10;

WITH table1 AS
(
SELECT ROW_NUMBER() OVER(ORDER BY DateTime DESC) AS RowNumber, 
       l.* 
  FROM StreamView l
  JOIN Friendships f ON f.Sender = @UserName OR f.Recipient = @UserName 
 WHERE l.Sender <> @UserName 
   AND l.Recipient <> @UserName 
   AND (   l.Sender = f.Recipient 
        OR l.Sender = f.Sender 
        OR l.Recipient = f.Sender 
        OR l.Recipient = f.Recipient) 
)
SELECT * 
  FROM table1 
 WHERE RowNumber >= @startat 
   AND RowNumber < @startat + @howmany;

The only thing that is missing is the distinct aspect, because that will cause problems with the row_number.

What you could do to resolve this is move the select statement into a function, which would perform the distinct selection, then select from this in the with statement.

CREATE FUNCTION dbo.GetRecords(@UserName NVARCHAR(MAX)) RETURNS TABLE
AS
  RETURN (
    SELECT DISTINCT l.* 
      FROM StreamView l
      JOIN Friendships f ON f.Sender = @UserName OR f.Recipient = @UserName 
     WHERE l.Sender <> @UserName 
       AND l.Recipient <> @UserName 
       AND (   l.Sender = f.Recipient 
            OR l.Sender = f.Sender 
            OR l.Recipient = f.Sender 
            OR l.Recipient = f.Recipient) 

  )
GO

Then the select in the with statement is something like:

SELECT ROW_NUMBER() OVER(ORDER BY DateTime DESC) AS RowNumber, 
       l.* 
  FROM dbo.GetRecords(@UserName)

Looks good, but note that it only works for SQL 2005 and later. — RickNZ, Jan 03 '12 at 07:49

Applying a 'LIMIT' like option to a written SQL Query

1 Answers1