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

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)) 
        || ' - ' || 
        UPPER(substr(max(LN), 1, 2 )) Page_Links,
        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 ??


Thanks for your inputs,
Sai.

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 

(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)

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  /

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

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

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.

Outstanding!!! I totally missed that one.

Many Thanks for your help.

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

pNo C_ID P_Links Count
=======================
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.

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

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.

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.