SQL Server 2008 Emulating the LIMIT Function

Question

I've got the following working query:

        string sqlString =
            "SELECT  * " +
            "FROM (SELECT ROW_NUMBER() OVER (ORDER BY Id DESC) AS RowNum, * " +
            "FROM StreamView " +
            "WHERE Recipient = @Recipient " +
            ") AS RowConstrainedResult " +
            "WHERE RowNum >= @startAt " +
            "AND RowNum < @howMany " +
            "ORDER BY RowNum;";

Which then returns the proper rows given a startAt and howMany variables. I would like to do the same with the query below:

        string sqlString =
        "SELECT DISTINCT l.* FROM Streams l " +
        "INNER 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;";

The query above works perfectly, but i'd like to get ranges instead of all the available rows. I need the same functionality of the first query.

Ideas? thanks.

What do you mean? the first query is in a c# class and is called like this (getstream(0,10,'someone')) - This returns rows 0 to 10 from the table. I'm trying to do the same with the second query keeping its results as they already are now but adding range functionality (used with lazy scrolling) — user1027620
– user1027620, Commented Nov 19, 2011 at 18:54
You are ordering by date -- so you want say the first 10 of the date ordered and then the 2nd 10 date ordered? — Hogan
– Hogan, Commented Nov 19, 2011 at 18:56
Yeah, I am ordering by date here just to get the latest ones first. But you know that. — user1027620
– user1027620, Commented Nov 19, 2011 at 18:58

Tom H · Accepted Answer · 2011-11-19 20:15:14Z

2

You should be able to put your original query in a subquery or a CTE and then select against that with a ROW_NUMBER() call. For example, something like (untested):

WITH CTE1 AS (
    SELECT DISTINCT
        l.*  -- List out all of the column names...
    FROM
    ...  -- Rest of your query, but you don't need the ORDER BY
),
CTE2 AS (
    SELECT
        CTE1.*,
        ROW_NUMBER() OVER (ORDER BY DateTime DESC) AS RowNum
    FROM
        CTE1
)
SELECT
    CTE2.*
FROM
    CTE2
WHERE
    RowNum BETWEEN @start_num and @end_num

I don't know what kind of a query plan SQL Server would use for this though, so performance might not be very good.

answered Nov 19, 2011 at 20:15

Tom H

47.6k15 gold badges90 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dani herrera Over a year ago

Why not all in cte1? select distinct l.*, ROW_NUMBER() OVER (ORDER BY DateTime DESC) AS RowNum ...

Tom H Over a year ago

I had a reason in my head when I wrote the query, but I'll be damned if I can think of it now :)

dani herrera Over a year ago

Perhaps due to "distinct" key word. ;)

Collectives™ on Stack Overflow

SQL Server 2008 Emulating the LIMIT Function

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related