problem in build query

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Wed, 07 Oct 2009 10:17 am

I need to build a query that returns the largest value until you change the value of a column. I think better with an example:
Code: Select all
with t1 as( select 1 Col1, 'A' Col2 from dual
UNION
select 2, 'B' from dual
UNION
select 3, 'B' from dual
UNION
select 4, 'A' from dual )
select
col2,
min (col1)||'-'||max(col1)
from
T1
GROUP BY
col2
This returns: the 1-4 B 2-3 but needed to return: the 1-1 B 2-3 to 4-4
esttevan
Location: Nova Hartz - RS

Poston Tue, 13 Oct 2009 2:48 pm

I don't understand the logic that exists in this result: 1-1 B 2-3 to 4-4 to 1-1--> being the 1 MIN that exists for the.
The 4-4--> being existing MAX 4 to the B-2 3-->???? What is this?
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Tue, 13 Oct 2009 3:09 pm

Actually I think it lacked information.
I need the major and minor whenever there are breaks.
In the example, the smallest and largest until Col2 (' A ') was changed was 1-1, já when col2 (' B ') changed again was 2-3 (minor and major) and when finished the col2 (' A ') the smallest and largest model was 4-4.

I tried with partition by at over but couldn't.

I'm waiting for help.
esttevan
Location: Nova Hartz - RS

Poston Mon, 19 Oct 2009 2:37 pm

Hi esttevan;

I took your example, I read your explanation and I couldn't figure out what you need.

What you call \"break\"?

What information you need, in what order and at what time?
Toad
Location: Seattle, WA

Matheus H. Gonçalves
www.toad.com.br
www.twitter.com/toadgeek

Poston Mon, 19 Oct 2009 3:10 pm

I called breaks the col2.
Whenever I need greater change and col2 lowest straight (col1), being able to repeat the value of col2.
esttevan
Location: Nova Hartz - RS

Poston Mon, 19 Oct 2009 4:13 pm

Old man, I will tell you that it's hard to understand what you need. Come on.

Assuming you have the sequence below:
Code: Select all
with t1 as( select 1 Col1, 'A' Col2 from dual
            UNION
            select 2, 'B' from dual
            UNION
            select 3, 'B' from dual
            UNION
            select 4, 'A' from dual
            UNION
            select 7, 'B' from dual
            UNION
            select 2, 'A' from dual
           )
as it should be output do you need?
Toad
Location: Seattle, WA

Matheus H. Gonçalves
www.toad.com.br
www.twitter.com/toadgeek

Poston Mon, 19 Oct 2009 4:30 pm

the col1 doesn't repeat, but it would be so the 1-1 B 2-3 to 4-4 B 7-7 to 2-2 but you can't repeat a further example:
Code: Select all
with t1 as( select 1 Col1, 'A' Col2 from dual
            UNION
            select 2, 'A' from dual
            UNION
            select 3, 'B' from dual
            UNION
            select 4, 'B' from dual
            UNION
            select 5, 'B' from dual
            UNION
            select 6, 'A' from dual
            UNION
            select 7, 'A' from dual
           )
the exit 1-B 2 3-5 to 6-7 I hope that helped
esttevan
Location: Nova Hartz - RS

Poston Mon, 19 Oct 2009 4:40 pm

Okay ... got it.

You need to list what number to start and where to end, until he change letter.

After where B starts and where (B) ends.

And so on as the letters are alternating, until the end.

You are using SQL or PL/SQL? Will use this in any procedure/function or would that result in a select?

Old, I don't think to do this in a select common ... I'm thinking here and anything I post here.
Toad
Location: Seattle, WA

Matheus H. Gonçalves
www.toad.com.br
www.twitter.com/toadgeek

Poston Sat, 12 Dec 2009 1:09 am

Buddy, if you still have a problem possible solution using the last example you gave is as follows: with the t1 (select 1 id, 1 Col1, Col2 ' A ' from dual UNION select 1, ' .2 ' from dual UNION select 2 .3, ' B ' from dual UNION select 2, .4 ' B ' from dual UNION select 2 .5, ' B ' from dual UNION select 3 .6, ' A ' from dual UNION select 3 .7, ' A ') from dual select col2, min (col1) || '-'||max (col1) from T1 GROUP BY col2, I created an id additional attribute in your select named id, this id is a sequence that only changes when the value of col1 is changed, if you populate the table in a program or PL/SQL is so easy to do that.

Created this attribute includes it in the group by using the same base created and fünf ...

1-2 B a| | 3-5 6-7 a| any questions I am available ...
borracha
Location: São Paulo - SP

Se trabalho fosse bom, não receberíamos para faze-lo...


  • See also
    Replies
    Views
    Last Post


      Return to SQL

      Who is online

      Users browsing this forum: No registered users and 12 guests