0

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
Community
  • 1
  • 1
Gareth Lewis
  • 751
  • 2
  • 15
  • 36
  • Would you like result of pagination to be random or whole set to be randomly ordered ? Meaning that same query will return different results for BETWEEN 0 AND 10 ? – Farfarak Oct 25 '12 at 18:48
  • Please make clear what do you mean by random order? You don't care about order, or you want order to be random through one pagination session, or you want every page to be selected from newly randomly shuffled rows? – Anri Oct 25 '12 at 18:49
  • @Anri, thanks for your comment. I realise how unclear I was. I want it to be random through one pagination session. It's a site to display my photographs, but as I don't want people to see the same old photos at the top every time, I wanted it to be random. However, as the page is loaded again after you click to the next page, it will randomise the photos again, and may display some of the same photos again. Maybe I need to rethink how I plan to do this. – Gareth Lewis Oct 25 '12 at 18:58
  • 1
    i would recommend to make an integer column "order" and just to fill it with random numbers from time to time and sort by it. Everything else like usual. – Anri Oct 25 '12 at 19:07
  • @Anri thanks. That sounds sensible. I think I'll write a stored procedure that randomly creates a new order, and schedule it in to run every so often. Sounds like a good solution. Would mark this as an answer if i could! – Gareth Lewis Oct 25 '12 at 19:11

2 Answers2

2
  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.

Anri
  • 6,175
  • 3
  • 37
  • 61
  • What is the exact meaning of "time to time"? If you shuffle the rows on each page visit, a unique visitor will not see all rows. If you shuffle the rows per visitor, you will need session and cookies to handle the situation and also more ideas for concurrent visitors. – Ali Sheikhpour Mar 07 '20 at 10:50
1

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.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Thanks. I think there is a flaw in my logic somewhere here. Is per @Anri comment to my question. I will give this a try though just to see what happens. I may need to rethink. – Gareth Lewis Oct 25 '12 at 19:00