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 Go to next message
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 #334720 is a reply to message #334719] Thu, 17 July 2008 14:48 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: Analytic Functions and Ranking/Ordering [message #334752 is a reply to message #334720] Thu, 17 July 2008 16:30 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
I think what anacedent is suggesting is post the table definition and population scripts or you aren't going to get any help.
Re: Analytic Functions and Ranking/Ordering [message #334765 is a reply to message #334752] Thu, 17 July 2008 22:07 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 #445158 is a reply to message #445153] Fri, 26 February 2010 04:36 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you show us the query we will tell us how to fix it.

Regards
Michel
Re: Analytic Functions and Ranking/Ordering [message #445169 is a reply to message #445153] Fri, 26 February 2010 05:24 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
preetm1 wrote on Fri, 26 February 2010 11:24
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

Looks like you are converting your number to a string.
Re: Analytic Functions and Ranking/Ordering [message #445184 is a reply to message #445169] Fri, 26 February 2010 06:01 Go to previous messageGo to next message
preetm1
Messages: 14
Registered: February 2010
Location: hyd
Junior Member
no i tried to use to_number function as well to print numbers only
Re: Analytic Functions and Ranking/Ordering [message #445185 is a reply to message #445184] Fri, 26 February 2010 06:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You need to use TO_NUMBER in the ORDER BY.

You also need to stop hijacking other peoples threads.
Re: Analytic Functions and Ranking/Ordering [message #445192 is a reply to message #334719] Fri, 26 February 2010 06:15 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: fatch column value on basis condition
Next Topic: Query rewrite
Goto Forum:
  


Current Time: Wed Sep 28 02:23:02 CDT 2016

Total time taken to generate the page: 0.09066 seconds