1

I am trying to update the existing table with one more column PageNo. The PageNo is dynamic value based on the total number of rows in the table. Say for an example I have 12 rows in the table. And while sending output from SQL, the customer want to send 4 rows per each page. So in my table

  1. the first 4 rows will be Pageno:1.
  2. Second set of 4 rows will be Page no:2

and so on..

Say for an example

SET @NoRowsPerPage=4
TotalNumber of rows in the table =12

So overall I have 3 pages (12/4) = 3:

Current output is as follows:

Id Name Age PageNo
100 sat 26 NULL
200 Apple 30 NULL
103 CHERRY 26 NULL
106 Orange 26 NULL
107 Raspberry 26 NULL
109 Lemon 36 NULL
389 Pineapple 28 NULL
107 MITUN 26 NULL
109 TEMPOR 26 NULL
389 KISN 26 NULL
110 Mandarin 26 NULL
111 BANANAG 26 NULL

Expected output:

Id Name Age PageNo
100 sat 26 1
200 Apple 30 1
103 CHERRY 26 1
106 Orange 26 1
107 Raspberry 26 2
109 Lemon 36 2
389 Pineapple 28 2
107 MITUN 26 2
109 TEMPOR 26 3
389 KISN 26 3
110 Mandarin 26 3
111 BANANAG 26 3

Can you please help. The idea is my first select query will fetch only the pageno :1 rows. second select query will pass only pageno:2 values...

Dale K
  • 25,246
  • 15
  • 42
  • 71
SATHK
  • 41
  • 5
  • This is likely something like `FLOOR(ROWNUMBER() OVER (ORDER BY )/@NoRowsPerPageBased) + 1`, but... what column(s) determine the ordering? – JNevill May 16 '23 at 18:11
  • Don't take this approach. It's always better to do pagination dynamically. Tomorrow if the sort order changes, your column will be of no use. Instead of this use `limit` and `offset` – Chetan Ahirrao May 16 '23 at 18:11
  • 1
    Since this is sql server, instead of `limit`/`offset` you use [`offset`/`fetch`](https://stackoverflow.com/a/9261762/2221001), which I would agree is better than stamping pagination to a table column. – JNevill May 16 '23 at 18:15
  • Hi JNevill: As of now no need to order, but can have ProductID or even can insert an identity column(incrementing) . Can you please give an example on Floor(RowNUMBER) – SATHK May 16 '23 at 18:15
  • 1
    When you talk about "the first 4 rows" that implies an order. First 4 ordered by what? – Martin Smith May 16 '23 at 18:16
  • "No need to order". Then you can do `FLOOR(ROW_NUMBER() OVER (ORDER BY 1)/@NoRowsPerPageBased) + 1` which will just put a row_number() on every record with no ordering. Each time you run it, you may get a different ordering, or you might not, depending on what way the wind is blowing, so to speak. – JNevill May 16 '23 at 18:17
  • @JNevill - Not suitable for pagination as there is no guarantee that the ordering mechanism will be the same between page 1 and page 2 and it would be totally valid for it to give you the exact same rows on "page 2" as you were already given on "page 1" – Martin Smith May 16 '23 at 18:18
  • Presumable @MartinSmith OP would stamp the ordering for all rows at once into the table since that's what they are asking to solve for, so it would be pre-decided and consistent. It's not a great idea when `offset`/`fetch` exists, but is sound-ish. – JNevill May 16 '23 at 18:20
  • Ah right. I was just skimming TBH and thought the `ORDER BY (SELECT 1)` suggestion was in conjunction with `offset/fetch`. Apologies – Martin Smith May 16 '23 at 18:22
  • @MartinSmith That is a terrible idea and I sort of love it. Can you do a window function inside `offset` or `fetch`? That would be wild. – JNevill May 16 '23 at 18:24
  • In the `ORDER BY` - https://dbfiddle.uk/iB4KtBKf but not in any of the other components. – Martin Smith May 16 '23 at 18:29
  • RANK() OVER() could be used, but again this is heavyweight to do what is essentially a client paging problem. – Conor Cunningham MSFT May 16 '23 at 21:53

1 Answers1

3

Don't take this approach. It's always better to do pagination dynamically. Tomorrow if the sort order changes, your column will be of no use. Instead of this use limit and offset.

Select * from my_table order by id asc limit :pageSize offset :pageNo*:pageSize;

Here pageSize and pageNo are parameters taken from the user.

For SQL server, you can use OFFSET, FETCH, NEXT

SELECT
    *
FROM
    my_table
ORDER BY
    id 
OFFSET :pageSize*:pageNo ROWS 
FETCH NEXT :pageSize ROWS ONLY;
Chetan Ahirrao
  • 1,454
  • 11
  • 16