SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Sorting based on count of rows

FlakeDec 11 2009 — edited Feb 14 2011

Hi all,

I have a problem at hand. A table BEERS looks as follows:

BEER_NAME      QUANTITY  PRIORITY
Budweiser      17        1
Budweiser      16        2
Corona         22        1
Heineken       9         1
Heineken       11        3
Guinness       15        2
Corona         2         2
Heineken       13        2

Here, I would like to arrange Beers in cases based on priority.
No beer of different brand should go in the same case.
No beer with same brand with different priority should go in the same case.
The maximum number of bottles that can be accomodated in a case is 10.

I wish to have a query which gives the result as follows:

BEER_NAME      QUANTITY  PRIORITY

Budweiser      10        1
Budweiser      7         1
Budweiser      10        2
Budweiser      6         2
Corona         10        1
Corona         10        1
Corona         2         1
Heineken       9         1
Heineken       10        3
Heineken       1         3
Guinness       10        2
Guinness       5         2
Corona         2         2
Heineken       10        2
Heineken       3         2

How would I get this?

oracle Database Version: Enterprise Edition 10.2.0.1

Regards,
...

189821

SQL>WITH t AS (
  2          SELECT 'Budweiser' BEER_NAME, 17 QUANTITY, 1 PRIORITY FROM DUAL UNION ALL
  3          SELECT 'Budweiser', 16, 2 FROM DUAL UNION ALL
  4          SELECT 'Corona', 22, 1 FROM DUAL UNION ALL
  5          SELECT 'Heineken', 9, 1 FROM DUAL UNION ALL
  6          SELECT 'Heineken', 11, 3 FROM DUAL UNION ALL
  7          SELECT 'Guinness', 15, 2 FROM DUAL UNION ALL
  8          SELECT 'Corona', 2, 2 FROM DUAL UNION ALL
  9          SELECT 'Heineken', 13, 2 FROM DUAL),
 10       q AS
 11       (SELECT MAX(quantity) AS maxq
 12          FROM t)
 13  SELECT   BEER_NAME, LEAST(quantity + 10 - l * 10, 10) AS case_quantity, PRIORITY
 14      FROM t,
 15           (SELECT     LEVEL AS l
 16                  FROM q
 17            CONNECT BY LEVEL <= CEIL(maxq / 10))
 18     WHERE l <= CEIL(QUANTITY / 10)
 19  ORDER BY 1, 3, 2 DESC;

BEER_NAME CASE_QUANTITY   PRIORITY
--------- ------------- ----------
Budweiser            10          1
Budweiser             7          1
Budweiser            10          2
Budweiser             6          2
Corona               10          1
Corona               10          1
Corona                2          1
Corona                2          2
Guinness             10          2
Guinness              5          2
Heineken              9          1
Heineken             10          2
Heineken              3          2
Heineken             10          3
Heineken              1          3

Urs

Flake

Thanks buddy,

You got it. With your permission, I slightly replaced the query as follows:

WITH t AS (
  select * from beers),
        q AS
        (SELECT MAX(quantity) AS maxq
           FROM t)
   SELECT   BEER_NAME, LEAST(quantity + 10 - l * 10, 10) AS case_quantity, PRIORITY
       FROM t,
            (SELECT     LEVEL AS l
                   FROM q
             CONNECT BY LEVEL <= CEIL(maxq / 10))
      WHERE l <= CEIL(QUANTITY / 10)
   ORDER BY 1, 3, 2 DESC;

Thanks for taking pain to write a query.

Regards,
...

Aketi Jyuuzou

I like model clause :D

WITH t AS (
SELECT 'Budweiser' BEER_NAME, 17 QUANTITY, 1 PRIORITY FROM DUAL UNION ALL
SELECT 'Budweiser', 16, 2 FROM DUAL UNION ALL
SELECT 'Corona', 22, 1 FROM DUAL UNION ALL
SELECT 'Heineken', 9, 1 FROM DUAL UNION ALL
SELECT 'Heineken', 11, 3 FROM DUAL UNION ALL
SELECT 'Guinness', 15, 2 FROM DUAL UNION ALL
SELECT 'Corona', 2, 2 FROM DUAL UNION ALL
SELECT 'Heineken', 13, 2 FROM DUAL)
select BEER_NAME,newQuan as QUANTITY,PRIORITY
  from t
 model return updated rows
partition by(RowNum as PID,BEER_NAME,PRIORITY)
dimension by(0 as soeji)
measures(QUANTITY,0 as newQuan)
rules ITERATE (999) UNTIL ((ITERATION_NUMBER+1)*10 > QUANTITY[0])
(newQuan[ITERATION_NUMBER] = Least(10,QUANTITY[0]-10*ITERATION_NUMBER))
ORDER BY BEER_NAME, PRIORITY, newQuan DESC;

BEER_NAME  QUANTITY  PRIORITY
---------  --------  --------
Budweiser        10         1
Budweiser         7         1
Budweiser        10         2
Budweiser         6         2
Corona           10         1
Corona           10         1
Corona            2         1
Corona            2         2
Guinness         10         2
Guinness          5         2
Heineken          9         1
Heineken         10         2
Heineken          3         2
Heineken         10         3
Heineken          1         3

Aketi Jyuuzou

I like recursive with clause better than model clause :-)
Hahaha I used PostgreSQL8.4 because I do not have Oracle11gR2 :8}

WITH recursive tmp AS (
SELECT 'Budweiser' BEER_NAME, 17 QUANTITY, 1 PRIORITY UNION ALL
SELECT 'Budweiser', 16, 2 UNION ALL
SELECT 'Corona', 22, 1 UNION ALL
SELECT 'Heineken', 9, 1 UNION ALL
SELECT 'Heineken', 11, 3 UNION ALL
SELECT 'Guinness', 15, 2 UNION ALL
SELECT 'Corona', 2, 2 UNION ALL
SELECT 'Heineken', 13, 2),
rec(BEER_NAME,PRIORITY,QUANTITY,rest) as(
select BEER_NAME,PRIORITY,Least(10,QUANTITY),
QUANTITY-Least(10,QUANTITY) from tmp
union all
select BEER_NAME,PRIORITY,Least(10,rest),
rest-Least(10,rest)
  from rec
 where rest > 0)
select*from rec
ORDER BY BEER_NAME, PRIORITY, QUANTITY DESC,rest desc;

 beer_name | priority | quantity | rest
-----------+----------+----------+------
 Budweiser |        1 |       10 |    7
 Budweiser |        1 |        7 |    0
 Budweiser |        2 |       10 |    6
 Budweiser |        2 |        6 |    0
 Corona    |        1 |       10 |   12
 Corona    |        1 |       10 |    2
 Corona    |        1 |        2 |    0
 Corona    |        2 |        2 |    0
 Guinness  |        2 |       10 |    5
 Guinness  |        2 |        5 |    0
 Heineken  |        1 |        9 |    0
 Heineken  |        2 |       10 |    3
 Heineken  |        2 |        3 |    0
 Heineken  |        3 |       10 |    1
 Heineken  |        3 |        1 |    0

Flake

Guys,

Great, and thanks for participation. Same thing is done in different ways and teaching many ways to do the same thing is in itself an opportunity to learn for the rest of us and also a preparation for next contingency. It's also an opportunity to showcase one's talent and receive appreciation.

Thanks,
...

839702

metzguar wrote:

<PRE class=jive-pre><CODE class="jive-code jive-java">SQL>WITH t AS ( 2 SELECT 'Budweiser' BEER_NAME, 17 QUANTITY, 1 PRIORITY FROM DUAL UNION ALL 3 SELECT 'Budweiser', 16, 2 FROM DUAL UNION ALL 4 SELECT 'Corona', 22, 1 FROM DUAL UNION ALL 5 SELECT 'Heineken', 9, 1 FROM DUAL UNION ALL 6 SELECT 'Heineken', 11, 3 FROM DUAL UNION ALL 7 SELECT 'Guinness', 15, 2 FROM DUAL UNION ALL 8 SELECT 'Corona', 2, 2 FROM DUAL UNION ALL 9 SELECT 'Heineken', 13, 2 FROM DUAL), 10 q AS 11 (SELECT MAX(quantity) AS maxq 12 FROM t) 13 SELECT BEER_NAME, LEAST(quantity + 10 - l * 10, 10) AS case_quantity, PRIORITY 14 FROM t, 15 (SELECT LEVEL AS l 16 FROM q 17 CONNECT BY LEVEL <= CEIL(maxq / 10)) 18 WHERE l <= CEIL(QUANTITY / 10) 19 ORDER BY 1, 3, 2 DESC; BEER_NAME CASE_QUANTITY PRIORITY--------- ------------- ----------Budweiser 10 1Budweiser 7 1Budweiser 10 2Budweiser 6 2Corona 10 1Corona 10 1Corona 2 1Corona 2 2Guinness 10 2Guinness 5 2Heineken 9 1Heineken 10 2Heineken 3 2Heineken 10 3Heineken 1 3</CODE></PRE>

Urs

Now I'll try it, I'm puzzled long, Thanks for your reply!

1 - 6

Locked Post

New comments cannot be posted to this locked post.

Locked on Mar 14 2011

Added on Dec 11 2009

6 comments

1,660 views

SQL & PL/SQL

Sorting based on count of rows

Comments

Post Details