Very Slick
February 7, 2008 - 1pm Central time zone
Reviewer: Michael O from TX
This is the slickest query I've seen in an age.
No Proper results
February 7, 2008 - 3pm Central time zone
Reviewer: sai from NY, USA
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.
Followup February 7, 2008 - 3pm Central time zone:
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...
take your query:
(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 (Q)
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
February 8, 2008 - 1pm Central time zone
Reviewer: Jack from Raleigh, NC USA
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
Followup February 8, 2008 - 3pm Central time zone:
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
February 8, 2008 - 4pm Central time zone
Reviewer: Jack from Raleigh, NC USA
Outstanding!!! I totally missed that one.
Many Thanks for your help.
March 19, 2008 - 4pm Central time zone
Reviewer: sai from NJ
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.
Followup March 24, 2008 - 9am Central time zone:
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
March 27, 2008 - 4pm Central time zone
Reviewer: Sai from NJ
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
Followup March 30, 2008 - 8am Central time zone:
... 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
March 31, 2008 - 4pm Central time zone
Reviewer: Sai from NJ
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.
Followup March 31, 2008 - 4pm Central time zone:
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.
|