Home » SQL & PL/SQL » SQL & PL/SQL » Returning rows with a condition
Returning rows with a condition [message #298339] Tue, 05 February 2008 16:24 Go to next message
DanR627
Messages: 7
Registered: February 2008
Location: New York
Junior Member
I'm trying to return rows in a certain order, but one condition has to be met. More specifically, I've created a temp table to rank the sales of a certain group of products, with three subgroups. I want to return the top ten sellers overall, but I want to make certain that all three subgroups are represented in the top five. Right now, subgroup 3's best seller only ranks as 6th overall. I want to bump that to number five, and slide the others down to accomodate. Can you help?

Here is my result set (product_id, subgroup_id, sales, rank):
10004  1  149  1
10012  2  144  2
10011  2  129  3
10002  1  102  4
10007  1  100  5
10025  3   94  6
10006  1   88  7
10024  3   75  8
10018  2   72  9
10001  1   71 10

I want it to look like this:
10004  1  149  1
10012  2  144  2
10011  2  129  3
10002  1  102  4
10025  3   94  5
10007  1  100  6
10006  1   88  7
10024  3   75  8
10018  2   72  9
10001  1   71 10


I'm using rownum for rank, but here is my base query:
SELECT product_id, subgroup_id, sales
FROM temp_product_sales
ORDER BY sales DESC


I'd appreciate any help, and I'll also do my best to be an active member of this forum, now that I've joined. Thanks in advance.

[EDITED by LF: added formatting tags]

[Updated on: Wed, 06 February 2008 01:22] by Moderator

Report message to a moderator

Re: Returning rows with a condition [message #298353 is a reply to message #298339] Tue, 05 February 2008 22:12 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Quote:
SELECT product_id, subgroup_id, sales
FROM temp_product_sales
ORDER BY sales DESC



it will give the output always according as sales.so through it you cann't put the 100 after 94.


add your logic to your query to make your problem readable.


also post in prescribed format.
read how to format your post.
use preview button to verify it.

regards,
Re: Returning rows with a condition [message #298392 is a reply to message #298339] Wed, 06 February 2008 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a test case: create table and insert statements.
Also post your Oracle version (4 decimals).

Regards
Michel
Re: Returning rows with a condition [message #298428 is a reply to message #298339] Wed, 06 February 2008 01:51 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

But how it is possible for 94 to placed before 100 ? . On what basis you want this to happen ? Is it a specific to these records only ?

It looks like somebody want to misrepresent the data Smile

Thumbs Up
Rajuvan.

[Updated on: Wed, 06 February 2008 01:51]

Report message to a moderator

Re: Returning rows with a condition [message #298432 is a reply to message #298428] Wed, 06 February 2008 01:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But how it is possible for 94 to placed before 100 ? . On what basis you want this to happen ?

It is possible (I didn't analyze the example, just talking about the principle) if 94 is in a group that is not in top 5 but must be displayed, in this case 100 and 94 must be swapped (and this is the reason I move it in Expert forum).

Regards
Michel

[Updated on: Wed, 06 February 2008 02:17]

Report message to a moderator

Re: Returning rows with a condition [message #298596 is a reply to message #298339] Wed, 06 February 2008 12:38 Go to previous messageGo to next message
DanR627
Messages: 7
Registered: February 2008
Location: New York
Junior Member
I'm creating a temp table and pulling the data from there. I've created a procedure that determines sales for each product, but I want each product group represented in the top five.

The temp table that I've created I use for a bunch of different things. The columns are named f1, f2, f3.....up to f36. Half of the columns are VARCHAR2 and the other half are NUMBER. I use it for many different procedures that I've written. In this procedure, I fill the temp table like this:

INSERT INTO dans_temp (f1, f2, f3, f4)
VALUES (seq_no, n_product_id, n_subgroup_id, n_product_sales)


At the end of the proc, I delete from the table where f1 = seq_no, to clean out the table.

In the proc, where I'm ordering my results, it looks like this:

SELECT f2, f3, f4
FROM dans_temp
WHERE f1 = seq_no
ORDER BY f4 DESC   


In other sections of the proc, I limit it to ten results and generate the sales numbers.

Now, on to my problem. I want to make sure that all of the product groups are represented in the top five (as indicated in my original post), even if I have to jump one product over others whose sales are greater (product 10025, in my example). If someone could point me in the right direction, I'd be greatly appreciative. Sorry for the improper format of my original post, and any confusion.
Re: Returning rows with a condition [message #298597 is a reply to message #298596] Wed, 06 February 2008 12:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is far from an easy problem.
There are complex cases.
Imagine the 5th element is also the first element of a group, then you don't swap the 5th and say 6th one but have to remove the 4th one, shift the 5th one to 4th place and put the remaining one (note 6th above) at the 5th place.
And so on, if you have more groups...

I don't have much times to think about it, maybe someone else (Barbara when she'll come in a couple of hours?).
And I tend to find a solution faster when the poster helps me posting a test case and giving his Oracle version when I request it.
And this should be BEFORE we request it as well as the formating as per OraFAQ Forum Guide.

Regards
Michel

[Updated on: Wed, 06 February 2008 12:58]

Report message to a moderator

Re: Returning rows with a condition [message #298604 is a reply to message #298597] Wed, 06 February 2008 13:51 Go to previous messageGo to next message
DanR627
Messages: 7
Registered: February 2008
Location: New York
Junior Member
Michel Cadot wrote on Wed, 06 February 2008 12:52


I don't have much times to think about it, maybe someone else (Barbara when she'll come in a couple of hours?).
And I tend to find a solution faster when the poster helps me posting a test case and giving his Oracle version when I request it.
And this should be BEFORE we request it as well as the formating as per OraFAQ Forum Guide.

Regards
Michel



I appologize for not familiarizing myself with the standards of posting here. I joined in haste and, to my discredit, did not follow your rules. I deserve the browbeating. My oracle version is 10.2.0.2.0. I'm not certain how to post a test case without including a ton of code. Should I just include the CREATE TABLE statement for my temp table? It's 36 columns, and I only use four of them in this instance. All of the data types are NUMBER here.
Re: Returning rows with a condition [message #298607 is a reply to message #298604] Wed, 06 February 2008 13:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I'm not certain how to post a test case without including a ton of code.

Just the create table and insert statements for the example you gave in the first post.

Regards
Michel
Re: Returning rows with a condition [message #298612 is a reply to message #298339] Wed, 06 February 2008 15:32 Go to previous messageGo to next message
DanR627
Messages: 7
Registered: February 2008
Location: New York
Junior Member
Gotcha. Here is the table:

CREATE TABLE temp_sales
(product_id        NUMBER(10),
 product_group_id  NUMBER(10),
 product_sales     NUMBER(10))


Here are the inserts:

INSERT INTO temp_sales(product_id, product_group_id, product_sales)
VALUES (10004, 1, 149);

INSERT INTO temp_sales(product_id, product_group_id, product_sales)
VALUES (10012, 2, 144);

INSERT INTO temp_sales(product_id, product_group_id, product_sales)
VALUES (10011, 2, 129);

INSERT INTO temp_sales(product_id, product_group_id, product_sales)
VALUES (10002, 1, 102);

INSERT INTO temp_sales(product_id, product_group_id, product_sales)
VALUES (10007, 1, 100);

INSERT INTO temp_sales(product_id, product_group_id, product_sales)
VALUES (10025, 3, 94);

INSERT INTO temp_sales(product_id, product_group_id, product_sales)
VALUES (10006, 1, 88);

INSERT INTO temp_sales(product_id, product_group_id, product_sales)
VALUES (10024, 3, 75);

INSERT INTO temp_sales(product_id, product_group_id, product_sales)
VALUES (10018, 2, 72);

INSERT INTO temp_sales(product_id, product_group_id, product_sales)
VALUES (10001, 1, 71);


Here is my query to put a rank on them:

SELECT product_id, product_group_id, product_sales, rownum AS rank
FROM (SELECT *
      FROM temp_sales
      ORDER BY product_sales DESC)


Is there some function or logic to sort them by rank, but assure that each of the product_group_ids are represented in the top five, even if you have to disrupt the 'ORDER BY'? In this instance, it would only be ranks 5 and 6 switching places, but like was mentioned earlier, it could be more than that (ex. if product_group 3's top seller was ranked 10th, it would have to be switched to 5th place and knock everything between 5th and 9th down a notch to accomodate the switch).
Re: Returning rows with a condition [message #298657 is a reply to message #298612] Thu, 07 February 2008 00:39 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> SELECT product_id, product_group_id, product_sales, ROWNUM AS rank
  2  FROM   (SELECT product_id, product_group_id, product_sales,
  3  		    DECODE
  4  		     (ROW_NUMBER () OVER (ORDER BY group_rank, product_sales DESC),
  5  		      1, 1, 2, 1, 3, 1, 4, 1, 5, 1, 2) AS five_group
  6  	    FROM   (SELECT product_id, product_group_id, product_sales,
  7  			   DECODE
  8  			     (ROW_NUMBER () OVER (PARTITION BY product_group_id ORDER BY product_sales DESC),
  9  			      1, 1, 2) AS group_rank
 10  		    FROM   temp_sales)
 11  	    ORDER  BY five_group, product_sales DESC)
 12  WHERE  ROWNUM <= 10
 13  /

PRODUCT_ID PRODUCT_GROUP_ID PRODUCT_SALES       RANK
---------- ---------------- ------------- ----------
     10004                1           149          1
     10012                2           144          2
     10011                2           129          3
     10002                1           102          4
     10025                3            94          5
     10007                1           100          6
     10006                1            88          7
     10024                3            75          8
     10018                2            72          9
     10001                1            71         10

10 rows selected.

SCOTT@orcl_11g> 

Re: Returning rows with a condition [message #298659 is a reply to message #298657] Thu, 07 February 2008 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Neat one! Thumbs Up

Regards
Michel
Re: Returning rows with a condition [message #298668 is a reply to message #298657] Thu, 07 February 2008 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Taking Barbara's query at a starting point, we can rewrite it this way (to any number of rows in first part of the output, N1 number of rows that must contain at least one element from each group and N2 number of output rows):
SQL> Def N1=5
SQL> Def N2=10
SQL> WITH
  2    step1 AS ( -- Flag maximum of each group with higher precedence
  3      SELECT product_id, product_group_id, product_sales,
  4             CASE
  5               WHEN product_sales  
  6                    = MAX(product_sales) OVER (PARTITION BY product_group_id)
  7                 THEN 1
  8               ELSE 2
  9             END AS group_rank
 10      FROM temp_sales
 11    ),
 12    step2 AS ( -- Create 2 groups: 1 with first N1 rows and 2 with other rows
 13               -- Order the result set
 14      SELECT product_id, product_group_id, product_sales,
 15             CASE 
 16               WHEN ROW_NUMBER () OVER (ORDER BY group_rank, product_sales DESC) <= &N1
 17                 THEN 1
 18               ELSE 2
 19             END AS N1_group
 20      FROM step1
 21      ORDER BY N1_group, product_sales DESC 
 22    )
 23  SELECT product_id, product_group_id, product_sales, ROWNUM AS rank
 24  FROM step2
 25  WHERE  ROWNUM <= &N2
 26  /
PRODUCT_ID PRODUCT_GROUP_ID PRODUCT_SALES  RANK
---------- ---------------- ------------- -----
     10004                1           149     1
     10012                2           144     2
     10011                2           129     3
     10002                1           102     4
     10025                3            94     5
     10007                1           100     6
     10006                1            88     7
     10024                3            75     8
     10018                2            72     9
     10001                1            71    10

10 rows selected.

This query does not work if product_sales can have the same value. In this case, we have to order the rows with same product_sales. In the following, I choose to order them by product_id:
SQL> WITH
  2    step1 AS ( -- Flag maximum of each group with higher precedence
  3      SELECT product_id, product_group_id, product_sales,
  4             LEAST(ROW_NUMBER() 
  5                      OVER (PARTITION BY product_group_id 
  6                            ORDER BY product_sales DESC, product_id)
  7                  , 2) AS group_rank
  8      FROM temp_sales
  9    ),
 10    step2 AS ( -- Create 2 groups: 1 with first N1 rows and 2 with other rows
 11               -- Order the result set
 12      SELECT product_id, product_group_id, product_sales,
 13             CASE 
 14               WHEN ROW_NUMBER () OVER 
 15                      (ORDER BY group_rank, product_sales DESC, product_id) 
 16                    <= &N1
 17                 THEN 1
 18               ELSE 2
 19             END AS N1_group
 20      FROM step1
 21      ORDER BY N1_group, product_sales DESC 
 22    )
 23  SELECT product_id, product_group_id, product_sales, ROWNUM AS rank
 24  FROM step2
 25  WHERE  ROWNUM <= &N2
 26  /
PRODUCT_ID PRODUCT_GROUP_ID PRODUCT_SALES  RANK
---------- ---------------- ------------- -----
     10004                1           149     1
     10012                2           144     2
     10011                2           129     3
     10002                1           102     4
     10025                3            94     5
     10007                1           100     6
     10006                1            88     7
     10024                3            75     8
     10018                2            72     9
     10001                1            71    10

10 rows selected.

Regards
Michel
Re: Returning rows with a condition [message #298774 is a reply to message #298339] Thu, 07 February 2008 11:28 Go to previous message
DanR627
Messages: 7
Registered: February 2008
Location: New York
Junior Member
Very nice. Thanks alot.
Previous Topic: Is it possible?
Next Topic: Oracle Load processes
Goto Forum:
  


Current Time: Fri Dec 09 05:51:20 CST 2016

Total time taken to generate the page: 0.14753 seconds