• Questions
• # Query to obtain pagination links

Thanks for the question, sai.

Asked: February 06, 2008 - 11:26 am UTC

Last updated: March 31, 2008 - 4:48 pm UTC

Version: 10g

Viewed 1000+ times

Hi Tom,
I am trying to formulate a SQL query to obtain a pagination links to display "By Name" in alphabetically. The page limit being 200 records.

E.g

A - L | M - S | T - Z

Authors starting char "A" to "L" are less than( ~ 200 like 190 + ) or equal to 200. More complex situation In case a specific character span more than that limit then the links should be displayed as A - La | Le - Su | T - Z

So A - La has records less than( ~ 200 like 190 + ) or equal to 200. But there may not be any records for Lb so display the next link as Le where the record exists.

I am able to get the counts on the columns -

```SELECT  UPPER(SUBSTR(LN,1,1)) STARTS_WITH_CHAR,
COUNT(UPPER(SUBSTR(LN,1,1))) COUNT
FROM
(SELECT DISTINCT CI.LAST_NAME LN
FROM
PRODUCT_CON_ROLE PC,
CON_INFO CI,
PRODUCT_C_ENTITY_ID_MAPPING PCE
WHERE
CI.LAST_NAME IS NOT NULL
AND   CI.CON_ID = PC.CON_ID
AND   PC.ROLE_ID IN ('AU', 'MA')
AND   PC.PRODU_ID = PCE.PRODU_ID
AND   PCE.C_ID = 'XXX'
AND   PCE.M_TYPE = 'YY'
ORDER BY CI.LAST_NAME)
GROUP BY UPPER(SUBSTR(LN,1,1))
ORDER BY UPPER(SUBSTR(LN,1,1))```

Result : 26 rows

A 101
B 275
C 213
D 161
E 54
F 115
.
.
.

Also when inputed as SUBSTR(LN,1,2) in the above query, I get the first 2 characters of name -

Result : 224 rows

AA 1
AB 6
AC 5
.
.
BA 63
BE 56
.
.
.

I am trying to formulate query to get the following result set but not knowing how to do it.

Page No Start_Char End_Char Offset
1 A Ba 0
2 Be Br 163
3 Bu Cr 260
.
.
.

In above "Page No" is pseduo colum numbering and column "offset" is to provide the index of start and end is obtained from the next record. Obtaining the result set as above with readily help in displaying the links as

A - Ba | Be - Br | Bu - Cr ........

and also the corresponding displayed below on the page using the offset.

I am trying to use SUM of the count and condition but i dont know how to break when it reaches the max also the complexity with 2nd character level.

Can you let me know what is the possible query that can help to obtain the above result set.

Thanks,
Sai.

## and Tom said...

```ops\$tkyte%ORA10GR2> select substr( min(object_name), 1, 2) || ' - ' || substr( max(object_name), 1, 2 ), count(*), trunc( rn / 200 )
2    from (
3  select object_name, row_number() over (order by object_name) rn
4    from all_objects
5   where rownum <= 1234
6         )
7   group by trunc( rn / 200 )
8   order by 1
9  /

SUBSTR(   COUNT(*) TRUNC(RN/200)
------- ---------- -------------
AC - I_        199             0
I_ - I_        200             1
I_ - PL        200             2
PL - US        200             3
US - V\$        200             4
V\$ - V_        200             5
V_ - WA         35             6

7 rows selected.
```

## Rating

(8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

### Very Slick

Michael O, February 07, 2008 - 1:55 pm UTC

This is the slickest query I've seen in an age.

### No Proper results

sai, February 07, 2008 - 3:01 pm UTC

Thanks for your response. But when i translated my query as below to the you sent my results are not correct, they are 3 times.

======================================
```SELECT  trunc( rn / 200 ) Page_No,
UPPER(substr( min(LN), 1, 2))
|| ' - ' ||
COUNT(*) COUNT
FROM
(
SELECT DISTINCT UPPER(CI.LAST_NAME) LN,row_number() over (order by CI.LAST_NAME) rn
FROM
PRODUCT_CON_ROLE PC,
CON_INFO CI,
PRODUCT_C_ENTITY_ID_MAPPING PCE
WHERE
CI.LAST_NAME IS NOT NULL
AND   CI.CON_ID = PC.CON_ID
AND   PC.ROLE_ID IN ('AU', 'MA')
AND   PC.PRODU_ID = PCE.PRODU_ID
AND   PCE.C_ID = 'XXX'
AND   PCE.M_TYPE = 'YY'
AND   rownum <= 12345
ORDER BY UPPER(CI.LAST_NAME)
)
GROUP BY trunc( rn / 200 )
ORDER BY 1```

====================================

I observe that when i use "row_number() over (order by CI.LAST_NAME)" in subquery the results are 3 times. Can you clarify why it could be and

My changes - I have rownum<=12345 because my results are about 2900. If i put rownum<=1234 it fetches about 1200 records only. Is this correct?

Also We dont always want 2 characters in the page links in case the alphabet has only less records. For e,g A can have only 101 records since page accepts 200 records, the first link should be "A - Be" and so on. Can you let me know what logic should be incorporated to obtain the same.

One more question - "Order by 1" at the end of query is ordering by first column ??

Sai.

February 07, 2008 - 3:53 pm UTC

```SELECT DISTINCT UPPER(CI.LAST_NAME) LN,row_number() over (order by
CI.LAST_NAME) rn
FROM
PRODUCT_CON_ROLE PC,
CON_INFO CI,
PRODUCT_C_ENTITY_ID_MAPPING PCE
WHERE ```

think about it.... row_number() is *distinct*

so the distinct - does NOTHING...

```(SELECT DISTINCT CI.LAST_NAME LN
FROM
PRODUCT_CON_ROLE PC,
CON_INFO CI,
PRODUCT_C_ENTITY_ID_MAPPING PCE
WHERE
CI.LAST_NAME IS NOT NULL
AND   CI.CON_ID = PC.CON_ID
AND   PC.ROLE_ID IN ('AU', 'MA')
AND   PC.PRODU_ID = PCE.PRODU_ID
AND   PCE.C_ID = 'XXX'
AND   PCE.M_TYPE = 'YY'
ORDER BY CI.LAST_NAME)```

and lets call is "q"

```ops\$tkyte%ORA10GR2> select substr( min(object_name), 1, 2) || ' - ' || substr(
max(object_name), 1, 2 ), count(*), trunc( rn / 200 )
2    from (
3  select ln, row_number() over (order by object_name) rn
4    from (<b>Q</b>)
6         )
7   group by trunc( rn / 200 )
8   order by 1
9  /```

and yes, order by 1 orders by the first selected column

as for the shorter substr logic - come on - you see all of the information you have here right - understand what it does and you'll be able to come up with something...

### Reverse Order

Jack, February 08, 2008 - 1:12 pm UTC

Tom,
This little query is very powerful and will aid greatly in the application I am developing.
I do have one comment/question though. When I reverse the results by adding 'desc' in the order by clause, I notice my first range contains one less than my expected range of 10.

select min(batch_job_id) || ' - ' || max(batch_job_id) range
,count(*)
,trunc( rn / 10 )
from (select batch_job_id, row_number() over (order by batch_job_id desc) rn
from refresh_history
where rownum <= 10000 )
group by trunc( rn / 10 )
order by 1 desc;

RANGE COUNT(*) TRUNC(RN/10)
--------------- ---------- ------------
1280 - 1360 9 0
1180 - 1270 10 1
1170 - 1170 1 2

February 08, 2008 - 3:32 pm UTC

well, frankly, if you reverse engineer the query (that should be the FIRST thing you do, to understand it), you could fix this.

My first range ALWAYS has one less:
```ops\$tkyte%ORA10GR2> select substr( min(object_name), 1, 2) || ' - ' || substr(
max(object_name), 1, 2 ), count(*), trunc( rn / 200 )
2    from (
3  select object_name, row_number() over (order by object_name) rn
4    from all_objects
5   where rownum <= 1234
6         )
7   group by trunc( rn / 200 )
8   order by 1
9  /

SUBSTR(   COUNT(*) TRUNC(RN/200)
------- ---------- -------------
AC - I_        199             0
I_ - I_        200             1
I_ - PL        200             2
PL - US        200             3
```

because row_number() starts at 1, not zero...

so, make it start at zero :)

```ops\$tkyte%ORA10GR2> select substr( min(object_name), 1, 2) || ' - ' || substr( max(object_name), 1, 2 ), count(*), trunc( rn / 200 )
2    from (
3  select object_name, row_number() over (order by object_name)-1 rn
4    from all_objects
5   where rownum <= 1234
6         )
7   group by trunc( rn / 200 )
8   order by 1
9  /

SUBSTR(   COUNT(*) TRUNC(RN/200)
------- ---------- -------------
AC - I_        200             0
I_ - I_        200             1
I_ - PL        200             2
PL - US        200             3
US - V\$        200             4
V\$ - V_        200             5
V_ - WA         34             6

7 rows selected.
```

### Reverse Order

Jack, February 08, 2008 - 4:26 pm UTC

Outstanding!!! I totally missed that one.

sai, March 19, 2008 - 4:14 pm UTC

Hi Tom,
I am further working on obtaining alphabetical pagination links with regards to my changes in the implementation to the earlier one.

Here is the query i am working on -
===========================================================
select trunc( rn / 200 ),substr( min(LN), 1, 2) || ' - ' || substr(max(LN), 1, 2 ) PAGE_LINKS
,count(*)
from (select C_ID, row_number() over (order by C_ID) rn,LAST_NAME LN
from TAB_NAMES
where rownum <= 10000
order by C_ID)
group by trunc( rn / 200)
order by 1;
===========================================================

Where the inner query as below -

=======================
select C_ID, row_number() over (order by C_ID) rn,LAST_NAME LN
from TAB_NAMES
where rownum <= 10000
order by C_ID
=======================

Which results as below which is expected and fine.

C_ID Rn Ln
==============
0001 0 AAXXX
0001 1 AEXXX
0001 2 AYXXX
.
.
.
0001 6010 ZUXXX
0002 6011 AAXXX
0002 6012 AEXXX
0002 6013 AYXXX

From these results When executed with the outer query

=======================
0 0001 AA - AL 199
1 0001 AL - BE 200
2 0001 BE - SA 200
.
.
.
.
29 0001 ZA - ZU 200
30 0001 AA - ZW 200 --->> This record gets incorrect
31 0002

record with pNo as 30 gets incorrect as it clubs the page limit of 200 with other C_Id i.e., 0002. But We dont want to include the count of C_ID 0002 in C_ID 0001. And want to keep the count seperate.

Ideally the record on pNo 30 should be -
30 0001 ZU - ZW 11

For C_ID 0001 only 11 names are left before it switches to 0002 (C_ID)

In other words, We want to build the page links for all c_id order by the c_id's with a page limit of 200 ( able to achieve till this point) and dont want to mix the count/page links of c_ids (This is what our issue is how to break or switch when the C_ID changes).

Can you please suggest how to approach the above.

March 24, 2008 - 9:58 am UTC

you'd need to start from scratch here, I do not go back up and re-read stuff from weeks/months ago. (includes creating data - at least a very clear link to create the data, back into this page - every review has a link associated with it)

sounds like you need to PARTITION by c_id if you want things to reset - do you understand fully how analytics work?

Please play around with partition - to break the data up by C_id if you want things broken up by c_id. Given that you

a) know analytics
b) know what you want to do

this becomes just an exercise it applying A to B - so dig a little more into analytics, get the partition clause well understood and go from there.

### Partition along with analytic functions

Sai, March 27, 2008 - 4:43 pm UTC

Hi Tom,

Thanks for your partition. I am getting the partition rows in the inner query but not able to come through the outer query which involves analaytic functions.

I am trying to group such that maximum of 200 records can be displayed for each of C_ID

===========================================================
select trunc( rn / 200 ),substr( min(LN), 1, 2) || ' - ' || substr(max(LN), 1, 2 ) PAGE_LINKS
,count(*)
from (select C_ID, row_number() over (PARTITION by C_ID order by C_ID) rn,LAST_NAME LN
from TAB_NAMES
where rownum <= 10000
order by C_ID)
group by trunc( rn / 200)
order by 1;
===========================================================

Where the inner query as below -

=======================
select C_ID, row_number() over (PARTITION BY C_ID order by C_ID) rn,LAST_NAME LN
from TAB_NAMES
where rownum <= 10000
order by C_ID
=======================

Which results as below which is expected and fine.

C_ID Rn Ln
==============
0001 0 AAXXX
0001 1 AEXXX
0001 2 AYXXX
.
.
.
0001 6010 ZUXXX
0002 1 AAXXX -->> Row number started again with Partition
0002 2 AEXXX
0002 3 AYXXX

From the above results I want to group such that each link can have 200 records for each of c_ID.

I have tried and followed other details too but could not get to solve the above query to obtain correct results.

Analytic function does not allow me to apply Partition again on to it. Any suggestions.

Thanks,
Sai

March 30, 2008 - 8:41 am UTC

... I am trying to group such that maximum of 200 records can be displayed for each
of C_ID
...

I don't think that is true, aren't you trying to limit a page to 200, regardless of the number of c_id

as stated, start over, full example - pretend I haven't read your stuff for weeks (because that is the case).

since prior work doesn't answer your question, ignore it - do not include snippets of code that do not work - rather include detailed specifications of the goal, the need instead. then and only then can we work to a solution.

### Pagination Query with Partition based on C_ID

Sai, March 31, 2008 - 4:19 pm UTC

Hi Tom,

I don't think that is true, aren't you trying to limit a page to 200, regardless of the number of c_id

>> No I am not trying limit a page to 200 regardless of C_ID.

Consider each C_ID as a group. So with that group I want Page No.s & Count's such that each page can max out at 200.

I am trying to create VIEW of results for all C_ID's since only at runtime I will know for which C_ID I have to fetch the page links.

Hope you understand my objective.

March 31, 2008 - 4:48 pm UTC

nope, start over is what I said above, I'm not going to read this that and the other review to get caught up.

I'll keep saying that, or just start ignoring the request.

create table, insert into, detailed, very very very detailed explanation of everything. If you already typed it in - just cut and pasted it into a nice readable understandable statement of the problem.

### pagination with a twist

Rahul, April 16, 2013 - 12:58 pm UTC

Hi Tom,

I did read the above and other pagination queries but none of the m seem to resolve my problem. I am providing a sample data and will try to explain my problem.

I have a table whose data needs to be paginated. The data in the table needs to be grouped by group_id and the pagination needs to be such that no group is partitioned across pages. this means that the page size can even increase if the number of records in a group are more than the specified pagesize.

Eg.
--Create sample table
create table pagination
(txn_id number(8),
group_id number(8),
cnt number(8));

-- Sample Data
insert into pagination(txn_id,group_id,cnt) values(1,null,1);
insert into pagination(txn_id,group_id,cnt) values(2,null,1);
insert into pagination(txn_id,group_id,cnt) values(3,null,1);
insert into pagination(txn_id,group_id,cnt) values(4,null,1);
insert into pagination(txn_id,group_id,cnt) values(5,null,1);
insert into pagination(txn_id,group_id,cnt) values(6,1234,3);
insert into pagination(txn_id,group_id,cnt) values(7,1234,3);
insert into pagination(txn_id,group_id,cnt) values(8,1234,3);
insert into pagination(txn_id,group_id,cnt) values(9,1452,3);
insert into pagination(txn_id,group_id,cnt) values(10,1452,3);
insert into pagination(txn_id,group_id,cnt) values(11,1452,3);
insert into pagination(txn_id,group_id,cnt) values(12,7985,2);
insert into pagination(txn_id,group_id,cnt) values(13,7985,2);
insert into pagination(txn_id,group_id,cnt) values(14,3569,6);
insert into pagination(txn_id,group_id,cnt) values(15,3569,6);
insert into pagination(txn_id,group_id,cnt) values(16,3569,6);
insert into pagination(txn_id,group_id,cnt) values(17,3569,6);
insert into pagination(txn_id,group_id,cnt) values(18,3569,6);
insert into pagination(txn_id,group_id,cnt) values(19,3569,6);
------------------------------------------------------
I have just created sample data. Now let us consider the page size is 4 records per page.

As per the rule the records who dont have group_id should be treated as individual records.
Individual records should always be displayed first.
A page can have records from multiple groups Provided the page size is maintained.
All the records of a group should be included on the same
page.

So the expected query should display records as follows
===================Expected Result ====================

TXN_ID GROUP_ID CNT PAGE
1 null 1 1
2 null 1 1
3 null 1 1
4 null 1 1
5 null 1 2
6 1,234 3 2
7 1,234 3 2
8 1,234 3 2
9 1,452 3 3
10 1,452 3 3
11 1,452 3 3
12 7,985 2 4
13 7,985 2 4
14 3,569 6 5
15 3,569 6 5
16 3,569 6 5
17 3,569 6 5
18 3,569 6 5
19 3,569 6 5

please note : Cnt column indicates the number of records in the group.

Thanks
Rahul.

# More to Explore

##### Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.