Home » SQL & PL/SQL » SQL & PL/SQL » Analytic Functions and Ranking/Ordering (Oracle 10.1.0.5.0, RedHat Linux)
Analytic Functions and Ranking/Ordering [message #334719] |
Thu, 17 July 2008 14:47  |
just4ever
Messages: 1 Registered: July 2008
|
Junior Member |
|
|
Hi, I need to return an ordered list of documents. The documents may belong to a set id (optional) and if so, are either a "master" or a "duplicate" type. For each set there can be only one master but many duplicates. My goal is to group all the sets together such that each master is proceeded by its duplicates.
Table description:
document_master_duplicates
(
documentid,
duplicate_setid,
is_master
)
This needs to join to another table briefcase_documents which contains our set of documents. The briefcase / document relationship is many-to-many.
Table description:
briefcase_documents
(
briefcaseid,
documentid
)
There's also a documents table containing the documentid and among other things a page_count. In the following example I want to sort the documents first by page count but preserving the master/dupe grouping. Any documents which don't belong to a set or are just a duplicate without a master i want at the end of my set but also ordered by page count.
Here's an example set that I would want to order by:
DocumentId Page_Count SetId Is_Master
2002 2 1 0
2003 20 2 0
2008 20 NULL NULL
2010 20 4 0
2012 1 4 1
2001 5 1 1
2004 16 3 1
2011 17 4 0
2014 10 5 0
2009 9 NULL NULL
As you can see I have a little bit of everything here. Docs 2001 and 2002 are the typical set of 1 master and its duplicate. Docs 2010, 2011, and 2012 is the same just a set of 3. Doc 2004 is a master but without any duplicates. Docs 2003 and 2014 are duplicates without a master (these docs have a master in the table but that doc isn't in the set i need to order by). Docs 2008 and 2009 do not belong to a set and as such do not have a master/dupe type.
The result i'm looking to achieve will be ordered as follows:
DocumentId Page_Count SetId Is_Master
2012 1 4 1
2011 17 4 0
2010 20 4 0
2001 5 1 1
2002 2 1 0
2004 16 3 1
2009 9 NULL NULL
2014 10 5 0
2003 20 2 0
2008 20 NULL NULL
As I said above I first want to get the groupings of master/dupes and order ascending on the masters page count. For each duplicate of a master I then want to order the duplicates by page count. After I finished ordering all the master/dupe groups I then want to move on to the rest of the documents which will contain documents that don't belong to a set along with documents which are duplicates but have no master in my set. However, documents which are masters but without duplicates should have been ordered along with the other master/dupes groupings.
With this all in mind I have just been completely overwhelmed as to where to even start. Am I using analytic functions? Hierarchical stuff? I don't know and any help would be appreciated.
[Updated on: Thu, 17 July 2008 14:54] Report message to a moderator
|
|
|
|
|
Re: Analytic Functions and Ranking/Ordering [message #334765 is a reply to message #334752] |
Thu, 17 July 2008 22:07   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
So the first thing you want to sort by is the master page count.
Here's a SQL to get that piece of information:
SELECT duplicate_setid, page_count AS master_page_count
FROM document_master_duplicates a
JOIN documents b ON a.documentid = b.documentid
WHERE a.Is_master = 1
So the documents sorted by master page count would be:
SELECT dmd.document_id
, doc.page_count
, dmd.duplicate_setid
, dmd.is_master
FROM document_master_duplicates dmd
JOIN documents doc ON doc.documentid = dmd.documentid
LEFT JOIN (
SELECT duplicate_setid, page_count AS master_page_count
FROM document_master_duplicates a
JOIN documents b ON a.documentid = b.documentid
WHERE a.Is_master = 1
) mas ON mas.duplicate_setid = dmd.duplicate_setid
ORDER BY mas.master_page_count
, mas.duplicate_setid
Note the LEFT JOIN. For docs without a master, the master_page_count will be NULL - which should sort last as desired.
Now within each group, you wanted to sort by Page Count, so just add the page_count to the ORDER BY:
SELECT dmd.document_id
, doc.page_count
, dmd.duplicate_setid
, dmd.is_master
FROM document_master_duplicates dmd
JOIN documents doc ON doc.documentid = dmd.documentid
LEFT JOIN (
SELECT duplicate_setid, page_count AS master_page_count
FROM document_master_duplicates a
JOIN documents b ON a.documentid = b.documentid
WHERE a.Is_master = 1
) mas ON mas.duplicate_setid = dmd.duplicate_setid
ORDER BY mas.master_page_count
, mas.duplicate_setid
, doc.page_count
Of course, all of this SQL is untested.
Ross Leishman
|
|
|
Re: Analytic Functions and Ranking/Ordering [message #445153 is a reply to message #334719] |
Fri, 26 February 2010 04:24   |
preetm1
Messages: 14 Registered: February 2010 Location: hyd
|
Junior Member |
|
|
Hi,
Im using the row_number() to generate a sequence for a query output.
The sequence is geeting printed correctly if the query output is less than 10 rows.But if the query output is more than 10 rows the problem is arising and it is as below
1
10
11
12
..
..
2
20
21
22
..
..
30
31
32
..
..
I would want the sequnec to get printed as
1
2
3
..
..
10
11
12
..
..
20
21
22
..
..
30
31
32
..
..
looking forward for help on this issue.
Thanks in advance
|
|
|
|
Re: Analytic Functions and Ranking/Ordering [message #445169 is a reply to message #445153] |
Fri, 26 February 2010 05:24   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
preetm1 wrote on Fri, 26 February 2010 11:24Hi,
Im using the row_number() to generate a sequence for a query output.
The sequence is geeting printed correctly if the query output is less than 10 rows.But if the query output is more than 10 rows the problem is arising and it is as below
1
10
11
12
..
..
2
20
21
Looks like you are converting your number to a string.
|
|
|
|
|
Re: Analytic Functions and Ranking/Ordering [message #445192 is a reply to message #334719] |
Fri, 26 February 2010 06:15   |
preetm1
Messages: 14 Registered: February 2010 Location: hyd
|
Junior Member |
|
|
query tht im using
SELECT id,
REPLACE (REPLACE (schemaname.STRAGG2(distinct RPAD (curr), 2, ' ') || ')' || NVL (DECODE (filed5, NULL, field6, filed5), 'UNKNOWN') || '###' ||
' label1 ' || ': ' || filed1 || '###' ||
' label2 '|| ': ' || filed2 || '###' ||
RPAD (' label3' || ': '|| filed3 || '###' ||
RPAD (' label4' || ': '|| filed4, ',', CHR(10)), '###', CHR (10)) alias1
FROM
(SELECT /*+ no_unnest*/ data.pr_id, pst.name status,
MAX (DECODE (name, 'D', name, NULL)) filed1,
MAX (DECODE (name, 'R', name, NULL)) filed2,
MAX (DECODE (name, 'p', name, NULL)) filed3,
MAX (DECODE (name, 'l', name, NULL)) filed4,
filed5, filed6,
row_number() OVER (PARTITION BY id order by seq_no) curr
FROM tables
WHERE ..
AND ..
AND ..
GROUP BY id,seq_no,name
) react
GROUP BY id
|
|
|
Re: Analytic Functions and Ranking/Ordering [message #445198 is a reply to message #445192] |
Fri, 26 February 2010 07:04  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
So you do NOT have the result you posted, you have a string and order by sort with string algorithm.
You can order by "to_number(substr(alias1,instr(alias1,' ')-1)" or the like depending on the actual result.
Regards
Michel
|
|
|
Goto Forum:
Current Time: Tue Feb 11 10:22:24 CST 2025
|