Query help

Dear Experts,

Please help me in constructing query.

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?
sakthikumarAsked:
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.

Omid OmarkhailSenior Technical ConsultantCommented:
Can provide your requirement in excel file.
0
sdstuberCommented:
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
Guy Hengel [angelIII / a3]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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sakthikumarAuthor Commented:
if next value is one more than the previous value, then it becomes a set.
0
sdstuberCommented:
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

Your issues matter to us.

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

Start your 7-day free trial
Geert GOracle 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

Open in new window


output:
START_VALUE,END_VALUE
1,3
5,6
9,10
0
sakthikumarAuthor Commented:
Thank you, very simple, i thought this would be complex.
0
Geert GOracle dbaCommented:
simple ... so you don't have oracle 12 ?
0
Geert GOracle dbaCommented:
no i wouldn't
:)
0
Geert GOracle 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  physical reads
          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

Open in new window


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  physical reads
          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>

Open in new window


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

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

Since you already had a reply ....

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
sdstuberCommented:
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;

Open in new window


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>

Open in new window

0
Geert GOracle 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

Open in new window

0
Geert GOracle 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;

Open in new window

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.