1

Given a table like this:

with qtys as (
  select 3 as qty from dual union all
  select 10 as qty from dual union all
  select 9 as qty from dual union all
  select 7 as qty from dual union all
  select 9 as qty from dual union all
  select 3 as qty from dual
)

I want to find the greatest common value of QTY.

I have thought of transforming each of these into a range and then getting the value from the result set that has the same count as the count in the table QTYS.

Only I'm not certain how to do this, and I'd like to do it with plain SQL in Oracle 11g, not a DECLARE...BEGIN...END PL/SQL block.

I've searched around and found many different methods of generating a single range based on a number, but I haven't found anything that would generate multiple ranges from zero to the value in the table and union them together as a single result set.

For example, given the above table I think if I could turn it into this:

QTY
---
 1
 2
 3
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 1
 2
 3
 4
 5
 6
 7
 8
 9
 1
...

Then I could find the count(1) of each of these and match it to the count(1) of the table QTYS, and use that value:

select count(1)
, qty
from (
  -- resultant ranges
)
group by qty
having count(1) = (
  select count(1)
  from qtys
);

In the case of the example table above, QTYS, the result of this query would be 3.

Maybe there is a better way to do this that I'm not thinking of, but I'm not coming to any conclusions yet.

Any thoughts?

  • 1
    Why would the answer be 3 and not 9? – ypercubeᵀᴹ Jul 29 '15 at 0:19
  • What is a 'greatest common value' of a list of numbers? And how do you transform a number in a range? Are you looking for the minimum of your numbers? – miracle173 Jul 29 '15 at 6:52
  • @ypercube this is because the value must be common to all numbers, and 3 is the most common value. My WITH clause is missing a point that these quantities could be for different "items", so the meaning of this might change if the WITH clause were: with qtys as ( select 3 as qty, 'B' as product from dual union all select 10 as qty, 'A' as product from dual union all select 9 as qty, 'A' as product from dual union all select 7 as qty, 'A' as product from dual union all select 9 as qty, 'A' as product from dual union all select 3 as qty, 'B' as product from dual ) – nathandelane Aug 5 '15 at 17:21
  • So you want the minimum quantity, as in nathandelane's last query. – ypercubeᵀᴹ Aug 5 '15 at 17:25
2

Solved this with help from https://stackoverflow.com/questions/3664903/how-can-i-return-multiple-identical-rows-based-on-a-quantity-field-in-the-row-it

The resulting answer with the original table looks like this:

with qtys as (
  select 3 as qty from dual union all
  select 10 as qty from dual union all
  select 9 as qty from dual union all
  select 7 as qty from dual union all
  select 9 as qty from dual union all
  select 3 as qty from dual
)
, results as (
  select n qty
  from qtys x
  join (
    select rownum n
    from dual
    connect by level <= (
      select max(qty)
      from qtys
    )
  ) y on y.n <= x.qty
)
select max(qty) qty
from (
  select count(1)
  , qty
  from results
  group by qty
  having count(1) = (
    select count(1)
    from qtys
  )
);

I'm certain I can condense it a little, but it produces the desired result.

EDIT

I suppose looking closer at this taking the minimum would have produced the same, desired result:

with qtys as (
  select 3 as qty from dual union all
  select 10 as qty from dual union all
  select 9 as qty from dual union all
  select 7 as qty from dual union all
  select 9 as qty from dual union all
  select 3 as qty from dual
)
select min(qty) qty
from qtys;
  • actually you could "condense it a little" :-) – miracle173 Jul 29 '15 at 7:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.