CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Gareth Lewis
Gareth Lewis

Reputation: 751

SQL Query results pagination with random Order by in SQL Server 2008

I am trying to implement pagination to a page on my website that returns results from a database table.

Currently, it returns all rows in a random order. However, as my database is growing, I want to paginate these results instead of displaying them all on one page. However, I don't want to return all results just to display 20 records for instance. Depending on the page, I want take just the 20 records from the database that are relevant.

I'm following this tutorial: Tutorial

However, the I cannot use the query with the OFFSET clause, because the hosting uses SQL SERVER 2008. (It is introduced in 2012 i believe).

I tried following the answer to this Question, but I want the results in a random order, and I cannot do an ORDER BY on a derived table... so I'm a bit stuck for ideas!

Any help? Thanks!

This is what I currently have:

    SELECT Title, Filename, PhotoURL, Orientation, FolderName, SetURL, RowNum
      FROM (
            SELECT  p.Title, p.Filename, p.URL AS PhotoURL, p.Orientation, s.FolderName, s.URL AS SetURL, ROW_NUMBER() OVER (ORDER BY p.PhotoID) AS RowNum
          FROM  Photos p
                LEFT OUTER JOIN SetPhotos sp
                    ON sp.PhotoID = p.PhotoID
                LEFT OUTER JOIN [Sets] s
                    ON s.SetID = sp.SetID
         WHERE  p.Hide = 0
      ORDER BY  NEWID()
        ) AS PaginatedPhotos
 WHERE  PaginatedPhotos.RowNum BETWEEN 0 AND 10

Upvotes: 0

Views: 2157

Answers (2)

Anri
Anri

Reputation: 6265

  1. Add integer column 'order' to your table
  2. Write a code that fills this column in all rows with unique random numbers
  3. Run this code from time to time to shuffle your rows
  4. Make pagination as usual while sorting by 'order'

Keep in mind that the same rows can appear on different pages if you shuffle rows in the middle of someone paginating.

Upvotes: 2

Remus Rusanu
Remus Rusanu

Reputation: 294407

Just select TOP(pagesize). Since your order is random, requesting page=2 does not result in the page 2 of the original result that displayed page 1. In other words when the order is random and changes each time then page 1 is always correct for any page requested.

Upvotes: 1

Related Questions