# Query help

Dear Experts,

I have rows like this.

TABLE : samp

col1
---------
1
2
3
5
6
9
10

I need result like below.

1 3
5 6
9 10

Idea is, rows should be grouped into sets, and need min and max value in each set.

Let me know how to achieve this, need to use analytic funtions?
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Senior Technical ConsultantCommented:
Can provide your requirement in excel file.
0
Commented:
what defines a set?
That is, why are 1,2,3 part of a set but not 1,2,3,5  ?

Just guessing your data is grouped into  sets of 4, (1-4) (5-8) (9-12) etc
If my guess is correct then try this...

SELECT MIN(col1), MAX(col1)
FROM samp
GROUP BY FLOOR(col1 / 4)
ORDER BY 1

if your grouping sets are defined by something else, then please specify
0
Billing EngineerCommented:
I have written an article for this kind of queries: finding ranges, see here:
http://www.experts-exchange.com/Database/Miscellaneous/A_3952-ranges-gaps-overlaps-for-numbers-and-date-ranges.html

the code is written on SQL Server, but should work with the relevant "oracle" tweaks also on that DBMS
0
Author Commented:
if next value is one more than the previous value, then it becomes a set.
0
Commented:
in that case try this...

SELECT MIN(col1), MAX(col1)
FROM (SELECT col1, col1 - ROW_NUMBER() OVER (ORDER BY col1) grp FROM samp)
GROUP BY grp
ORDER BY 1;
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Oracle dbaCommented:
in oracle 12 there is the new pattern matching clause.
the guide : http://docs.oracle.com/database/121/DWHSG/pattern.htm#DWHSG8956

try this in an oracle 12 database

``````create table data (n number);

insert into data (n)
select level from dual connect by level < 11;

delete from data where n in (4, 7, 8);

select *
from data
match_recognize (
order by n
measures
STRT.N as start_value,
LAST(UP.n) as end_value
ONE ROW PER MATCH
PATTERN (STRT UP+)
DEFINE
UP AS UP.n = PREV(UP.n)+1
) MR
``````

output:
START_VALUE,END_VALUE
1,3
5,6
9,10
0
Author Commented:
Thank you, very simple, i thought this would be complex.
0
Oracle dbaCommented:
simple ... so you don't have oracle 12 ?
0
Oracle dbaCommented:
no i wouldn't
:)
0
Oracle dbaCommented:
i'd use my plan (on a 12) as it's just as inefficient as your analytic rownumbering

``````Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

15:05:51 test@TESTU12>set autot trace
15:05:58 test@TESTU12>select *
15:08:09   2  from data
15:08:09   3    match_recognize (
15:08:09   4      order by n
15:08:09   5      measures
15:08:09   6        STRT.N as start_value,
15:08:09   7        LAST(UP.n) as end_value
15:08:09   8      ONE ROW PER MATCH
15:08:09   9      PATTERN (STRT UP+)
15:08:09  10      DEFINE
15:08:09  11        UP AS UP.n = PREV(UP.n)+1
15:08:09  12      ) MR;

Execution Plan
----------------------------------------------------------
Plan hash value: 2220740601

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                                            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                     |      |     1 |    26 |     3   (0)| 00:00:01 |
|   1 |  VIEW                                                |      |     1 |    26 |     3   (0)| 00:00:01 |
|   2 |   MATCH RECOGNIZE SORT DETERMINISTIC FINITE AUTOMATON|      |     1 |    13 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL                                 | DATA |     1 |    13 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
7  consistent gets
0  redo size
671  bytes sent via SQL*Net to client
504  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
3  rows processed
``````

``````15:08:21 test@TESTU12>SELECT MIN(n), MAX(n)
15:09:26   2      FROM (SELECT n, n - ROW_NUMBER() OVER (ORDER BY n) grp FROM data)
15:09:26   3  GROUP BY grp
15:09:26   4  ORDER BY 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1451161479

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    26 |     3   (0)| 00:00:01 |
|   1 |  SORT ORDER BY        |      |     1 |    26 |     3   (0)| 00:00:01 |
|   2 |   HASH GROUP BY       |      |     1 |    26 |     3   (0)| 00:00:01 |
|   3 |    VIEW               |      |     1 |    26 |     3   (0)| 00:00:01 |
|   4 |     WINDOW SORT       |      |     1 |    13 |     3   (0)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| DATA |     1 |    13 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
7  consistent gets
0  redo size
663  bytes sent via SQL*Net to client
504  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
2  sorts (memory)
0  sorts (disk)
3  rows processed

15:09:32 test@TESTU12>
``````

your code requires more steps to complete
> so why would i use your query ?

or you didn't try out 12c yet ?
0
Oracle dbaCommented:
you'll have to face it ... a split is in order ...
0
Commented:
I deleted my comment, but apparently not fast enough.

Yes - of course I tried it on 12c.  I wouldn't post a comparison comment if I didn't already have results.

I didn't care about the plans, I measured the actual execution statistics.

The pattern match did fewer sort operations, but despite that the analytic still consumed slightly -but consistently less cpu.  For very small data sets, both versions round down to 0 so I had to ramp it up.

That's why I originally posted a comment that the analytics were probably a better choice; but later I deleted it because - while my results were definitive and consistent - they weren't dramatic enough to convince me that they would always be so.

With other data, other cpu,memory,io it's possible the pattern matching might be better.  I just couldn't prove it.  I only had my results to work with and despite supporting my statement I decided it wasn't strong enough to keep the post.

Sorry for the confusion.

A split seems like an odd choice to me.  The individual posts don't contribute to a solution.  Only one answer is going to be used.  So it's not split-worthy. It's an either-or.  If the asker went with pattern matching then you get everything, if they go with analytics then I do.  If there was a third option taken - then that one.  Had the question been "what are some various options" then yes a split would seem reasonable; but again only if the asker could use all of the suggestions.

If it turned out the asker was still running oracle 7.3 then neither of our answers would qualify even though they are both very reasonable for modern systems.

If you want to RA for a split, I won't object, I just don't think it applies here.
0
Commented:
on further review - I think I would object.

The comparitive performance is irrelevant

The pattern match query produces (what I think are) incorrect results

``````DROP TABLE data PURGE;

CREATE TABLE data (n NUMBER);

INSERT INTO data(n)
SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL < 11;

DELETE FROM data
WHERE n IN (2,
3,
5,
7);

COMMIT;

SELECT *
FROM data MATCH_RECOGNIZE(
ORDER BY n
MEASURES strt.n AS start_value, LAST(UP.n) AS end_value
ONE ROW PER MATCH
PATTERN (strt UP+)
DEFINE UP AS UP.n = prev(UP.n) + 1
) mr;

SELECT MIN(n), MAX(n)
FROM (SELECT n, n - ROW_NUMBER() OVER (ORDER BY n) grp FROM data)
GROUP BY grp
ORDER BY 1;
``````

my results from the above...

``````Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> DROP TABLE data PURGE;

Table dropped.

SQL>
SQL> CREATE TABLE data (n NUMBER);

Table created.

SQL>
SQL> INSERT INTO data(n)
2          SELECT LEVEL
3            FROM DUAL
4      CONNECT BY LEVEL < 11;

10 rows created.

SQL>
SQL> DELETE FROM data
2        WHERE n IN (2,
3                    3,
4                    5,
5                    7);

4 rows deleted.

SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT *
2    FROM data MATCH_RECOGNIZE(
3                  ORDER BY n
4                  MEASURES strt.n AS start_value, LAST(UP.n) AS end_value
5                  ONE ROW PER MATCH
6                  PATTERN (strt UP+)
7                  DEFINE UP AS UP.n = prev(UP.n) + 1
8              ) mr;

START_VALUE  END_VALUE
----------- ----------
8         10

SQL>
SQL>   SELECT MIN(n), MAX(n)
2      FROM (SELECT n, n - ROW_NUMBER() OVER (ORDER BY n) grp FROM data)
3  GROUP BY grp
4  ORDER BY 1;

MIN(N)     MAX(N)
---------- ----------
1          1
4          4
6          6
8         10

SQL>
``````
0
Oracle dbaCommented:
i figured it was a little more complex than a simple min and max

this also gives the correct results ... i don't think it will perform very well
but it works on 11 too

``````with cte as (
select distinct prior n as pn, n, sys_connect_by_path(to_char(n), ',') p, level alevel
from data
where level > 1
and connect_by_isleaf = 1
connect by n = prior n +1
),
cte2 as (
select pn, n, p, alevel, max(alevel) over (partition by pn, n order by pn) maxlevel
from cte)
select
substr(p, 2, instr(p, ',', 2, 1)-2) start_num,
substr(p,  instr(p, ',', -1)+1) end_num
from cte2
where alevel = maxlevel
``````
0
Oracle dbaCommented:
on a second observation ( a range has a different start and end value)

``````SELECT MIN(n), MAX(n)
FROM (SELECT n, n - ROW_NUMBER() OVER (ORDER BY n) grp FROM data)
GROUP BY grp
having min(n) <> max(n)
ORDER BY 1;
``````
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.