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.