## Forum Stats

• 3,734,031 Users
• 2,246,861 Discussions

Discussions

#### Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

# Sorting based on count of rows

Member Posts: 264
edited February 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,
...

• 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
• 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,
...
• Member Posts: 1,072 Bronze Badge
I like model clause
```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```
• 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```
• 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,
...
• 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