Getting messages 1-20, 21-40,... from a database

Question

I'm trying to build a mailbox where we can group the messages in x. If you put x to 20 you'll see messages 1-20 on the first page, opening the second page will show message 21-40 etc.

How do I efficiently query this? The best I could come up with is this:

select top 20 * 
from tbl_messages
where 
tnr_id not in
(
    select top 40 tnr_id   —20/40/60/80/…
    from tbl_messages
    order by dt_made desc, tnr_id desc
)
order by dt_made desc, tnr_id desc

Is there a more efficient way to do this? Databases used are SQL server, oracle & sybase.

Answer 1

In Oracle:

SELECT  *
FROM    (
        SELECT  t.*, rownum AS rn
        FROM    tbl_messages t
        ORDER BY
                dt_made DESC, tnr_id DESC
        )
WHERE   rn > 40
        AND rownum <= 20

In SQL Server 2005 and above:

DECLARE @start INT
DECLARE @pagesize INT
SET @start = 40
SET @pagesize = 20  

SELECT  *
FROM    (
        SELECT  TOP (@start + @pagesize)
                t.*, ROW_NUMBER() OVER (ORDER BY dt_made DESC, tnr_id DESC) AS rn
        FROM    tbl_messages t
        ORDER BY
                dt_made DESC, tnr_id DESC
        ) q
WHERE   rn > @start

ROW_NUMBER is supported by Oracle too, but due to implementation details is a little bit less efficient than rownum.

See this article in my blog for performance comparison:

• Oracle: ROW_NUMBER vs ROWNUM

Update:

If you can tolerate some discrepancies resulted from the concurrent updates, you can remember the last record on the current page of the client side and use it to get the next results faster:

SELECT  TOP 20 *
FROM    tbl_messages t
WHERE   dt_made <= @last_dt_made
        AND NOT (dt_made = @last_dt_made AND tnr_id >= @last_tnr_id)
ORDER BY
        dt_made DESC, tnr_id DESC

Answer 2

for SQL Server, try this:

--set up a table to page through:
DECLARE @TestTable  table (TableID int )

INSERT INTO @TestTable (TableID)
SELECT TOP 1000 row_number() over(order by t1.number) as N
FROM master..spt_values t1 
    CROSS JOIN master..spt_values t2

--set page location and size
DECLARE @Start  int
DECLARE @Size   int

SET @Start=40
SET @Size=20

--return data for that page:
SELECT
    *
    FROM (SELECT 
              v.*, ROW_NUMBER() OVER (ORDER BY TableID) AS RowNumber
              FROM  @TestTable  v
         ) dt
    WHERE RowNumber>=@Start AND RowNumber<@Start+@Size

output:

TableID     RowNumber
----------- --------------------
40          40
41          41
42          42
43          43
44          44
45          45
46          46
47          47
48          48
49          49
50          50
51          51
52          52
53          53
54          54
55          55
56          56
57          57
58          58
59          59

(20 row(s) affected)

Answer 3

For SQL Server - maybe a bit more flexible, lets you specify page number and messages per page

DECLARE @pageNumber int, @messagesPerPage int
SET @pageNumber = 3
SET @messagesPerPage = 20

SELECT *
  FROM (
        SELECT t.*, ROW_NUMBER() OVER (ORDER BY [dt_made] DESC, [tnr_id] DESC) AS __RN
          FROM tbl_messages t
       ) iDat
 WHERE __RN BETWEEN (@pageNumber - 1) * @messagesPerPage AND @pageNumber * @messagesPerPage
 ORDER BY dt_made DESC, tnr_id DESC