Forum Stats

  • 3,734,031 Users
  • 2,246,861 Discussions
  • 7,857,002 Comments

Discussions

Sorting based on count of rows

Flake
Flake Member Posts: 264
edited February 2011 in SQL & PL/SQL
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,
...

Answers

  • 189821
    189821 Member Posts: 656
    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
    Flake Member Posts: 264
    edited December 2009
    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
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    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
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited December 2009
    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
    Flake Member Posts: 264
    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
    839702 Member Posts: 1
    metzguar wrote:

    <PRE class=jive-pre><CODE class="jive-code jive-java">SQL&gt;WITH t AS ( 2 SELECT <FONT color=navy>'Budweiser'</FONT> BEER_NAME, 17 QUANTITY, 1 PRIORITY FROM DUAL UNION ALL 3 SELECT <FONT color=navy>'Budweiser'</FONT>, 16, 2 FROM DUAL UNION ALL 4 SELECT <FONT color=navy>'Corona'</FONT>, 22, 1 FROM DUAL UNION ALL 5 SELECT <FONT color=navy>'Heineken'</FONT>, 9, 1 FROM DUAL UNION ALL 6 SELECT <FONT color=navy>'Heineken'</FONT>, 11, 3 FROM DUAL UNION ALL 7 SELECT <FONT color=navy>'Guinness'</FONT>, 15, 2 FROM DUAL UNION ALL 8 SELECT <FONT color=navy>'Corona'</FONT>, 2, 2 FROM DUAL UNION ALL 9 SELECT <FONT color=navy>'<font face="tahoma,verdana,sans-serif" size="1" color="#000">Heineken</font>'</FONT>, 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 &lt;= CEIL(maxq / 10)) 18 WHERE l &lt;= CEIL(QUANTITY / 10) 19 ORDER BY 1, 3, 2 DESC;&nbsp;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!
This discussion has been closed.