Re: difficult select- question

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Mon, 26 Oct 2009 20:27:11 +0100
Message-ID: <4ae5f810$0$22424$426a34cc_at_news.free.fr>

"Andreas Mosmann" <mosmann_at_expires-31-10-2009.news-group.org> a écrit dans le message de news: 1256558932.12_at_user.newsoffice.de...
| Hi ng,
|
| I hope, there is someone with a good idea concerning the following
| question:
|
| There is a sorted Table (or view) like the following:
| F1|F2|F3|F4
| AA|BB|10|20
| AA|BB|20|30
| AA|BB|40|50
| AA|BB|50|60
| AA|BB|60|70
| AA|CC|70|80
| AA|CC|80|90
|
| As you can see there are some rows, that are connected by F3=prior F4,
| but there are leaks too. I need a result as the following:
|
| AA|BB|10|30|2 parts
| AA|BB|40|70|3 parts
| AA|CC|70|90|2 parts
|
| I know it is easy to solve by programming.
| I think it is possible to do this with inline view and hierarchical
| query.
| I can imagine a way by select min(F3), max(F4) ... where exists (enough
| connecting data in the middle).
|
| But what is the best (SQL-)way to solve this? (There actually are about
| 15000 lines, the estimated result probably should not have more than 100
| lines)
| I could do it by PL/SQL, but I am interested in SQL, if exists
|
| Andreas Mosmann
|
|
|
|
|
| --
| wenn email, dann AndreasMosmann <bei> web <punkt> de

SQL> select connect_by_root f1 f1, f2,

  2         connect_by_root f3 f3, f4,
  3         (f4-connect_by_root f3)/10 cnt1,
  4         length(sys_connect_by_path(f2,'/'))
  5         - length(replace(sys_connect_by_path(f2,'/'),'/','')) cnt2

  6 from t
  7 where connect_by_isleaf = 1
  8 connect by prior f1 = f1 and prior f2 = f2 and prior f4 = f3   9 start with (f1,f2,f3) not in (select f1,f2,f4 from t)  10 /
F1 F2 F3 F4 CNT1 CNT2 -- -- ---------- ---------- ---------- ----------

AA BB         10         30          2          2
AA BB         40         70          3          3
AA CC         70         90          2          2

3 rows selected.

Regards
Michel Received on Mon Oct 26 2009 - 14:27:11 CDT