Home » SQL & PL/SQL » SQL & PL/SQL » Counting followed last ocurrences (Oracle Database 11g)
Counting followed last ocurrences [message #589345] Thu, 04 July 2013 09:39 Go to next message
OJogador13
Messages: 9
Registered: October 2008
Junior Member
Hi,

Can someone help me with the following conundrum.

Having the Following information:

Col A Col B
---------------
134 | 1049
0 | 1050
12 | 1051
0 | 1052
0 | 1053
0 | 1054
0 | 1055
0 | 1056
0 | 1057

What I want is to count the number of the similar occurrences on Col A starting from the bottom and stopping at the first that is different.
Taking the example above I would get 6, that is the number of repeated "0" from the last value "1057" until "1052".

I hope I was clear enough, if anyone needs further exemplification I am more than happy to provide.


Thanks in advance,
Frederico C.

[Updated on: Thu, 04 July 2013 09:40]

Report message to a moderator

Re: Counting followed last ocurrences [message #589348 is a reply to message #589345] Thu, 04 July 2013 09:44 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Counting followed last ocurrences [message #589351 is a reply to message #589348] Thu, 04 July 2013 10:08 Go to previous messageGo to next message
OJogador13
Messages: 9
Registered: October 2008
Junior Member
Thanks BlackSwan, I´ve search on google and the forum and could not find any help. Although there is a chance I'm missing a key word.

I´ve also created the following sql script to help create a table with information replicating the situation above.

I´me very new to the forum I hope everything is in order, if not please tell me so I'll be more than happy to correct the post.

CREATE TABLE case_test
(
 COL_A NUMBER(5),
 COL_B NUMBER(5)
 );

INSERT INTO case_test(COL_A, COL_B)
VALUES (134,1049);
INSERT INTO case_test(COL_A, COL_B)
VALUES (0,1050);
INSERT INTO case_test(COL_A, COL_B)
VALUES (1,1051);
INSERT INTO case_test(COL_A, COL_B)
VALUES (0,1052);
INSERT INTO case_test(COL_A, COL_B)
VALUES (0,1053);
INSERT INTO case_test(COL_A, COL_B)
VALUES (0,1054);
INSERT INTO case_test(COL_A, COL_B)
VALUES (0,1055);
INSERT INTO case_test(COL_A, COL_B)
VALUES (0,1056);
INSERT INTO case_test(COL_A, COL_B)
VALUES (0,1057);

select * from case_test;

DROP table case_test;


Best Regards,
Frederico
Re: Counting followed last ocurrences [message #589353 is a reply to message #589351] Thu, 04 July 2013 10:12 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
rows in a table are like balls in a basket.

starting with a basket full of balls, which ball is at the bottom of the basket? (which ball is the "last" ball?)

rows in a table have no inherent order.

[Updated on: Thu, 04 July 2013 10:12]

Report message to a moderator

Re: Counting followed last ocurrences [message #589354 is a reply to message #589353] Thu, 04 July 2013 10:21 Go to previous messageGo to next message
OJogador13
Messages: 9
Registered: October 2008
Junior Member
Sorry I did not understand, do you mean it´s not possible?

I was not explicit but col_B is purposely ordered. I've managed to think of some solutions using "LAST_VALUE" or "LEAD" using ordey by to count the next row if the value is the same. Unfortunately I could not make one to work.



Re: Counting followed last ocurrences [message #589356 is a reply to message #589354] Thu, 04 July 2013 10:32 Go to previous messageGo to next message
cookiemonster
Messages: 11001
Registered: September 2008
Location: Rainy Manchester
Senior Member
How about you show us what you tried and tell us exactly what the expected output should be.
Re: Counting followed last ocurrences [message #589357 is a reply to message #589356] Thu, 04 July 2013 10:50 Go to previous messageGo to next message
OJogador13
Messages: 9
Registered: October 2008
Junior Member
Thanks for the interest.

Taking the example above, I want the return to be a single value, In this case "6".

The value would refer to the number(count) of subsequent "0" values on COL_A counting from the last value "1057" until "1052" (ordered by COL_B).
Although there are other "0" values on the table they would be ignored by the count, since there is a different value between them.

I hope I was clear, if not I´m more that happy to try and explain it better.

Best Regards,
Re: Counting followed last ocurrences [message #589358 is a reply to message #589357] Thu, 04 July 2013 11:17 Go to previous messageGo to next message
Michel Cadot
Messages: 59297
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is clear and we know how to do it but we ask you to show us what you tried then you will learn.

Note: There are several ways to do it.

Regards
Michel
Re: Counting followed last ocurrences [message #589359 is a reply to message #589358] Thu, 04 July 2013 11:29 Go to previous messageGo to next message
OJogador13
Messages: 9
Registered: October 2008
Junior Member
Eehehe, I just want one, using a single table access.

Re: Counting followed last ocurrences [message #589362 is a reply to message #589359] Thu, 04 July 2013 12:35 Go to previous messageGo to next message
Michel Cadot
Messages: 59297
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So what are your tries?

Regards
Michel
Re: Counting followed last ocurrences [message #589363 is a reply to message #589362] Thu, 04 July 2013 13:01 Go to previous messageGo to next message
OJogador13
Messages: 9
Registered: October 2008
Junior Member
Hi,

I´ve managed to put it to work, but accessing the table multiple times.

select COUNT(1)  from case_test
 where col_b between (select max(col_b) from case_test where COL_A <> 0)
                     and
                       (select max(col_b) from case_test);


I think the same is possible using analytic functions like "LAST" unfortunately I still have not managed to put an example to work.

Nonetheless I´ll keep trying.


Re: Counting followed last ocurrences [message #589365 is a reply to message #589363] Thu, 04 July 2013 13:06 Go to previous messageGo to next message
Michel Cadot
Messages: 59297
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your query does not give the correct result: 7 instead of 6. It also counts (1051,1).
In addition you hard coded 0 in your query, what if the last col_b is not 0?

Regards
Michel
Re: Counting followed last ocurrences [message #589367 is a reply to message #589365] Thu, 04 July 2013 13:42 Go to previous messageGo to next message
OJogador13
Messages: 9
Registered: October 2008
Junior Member
Hi,

I´m ok with the 0 hard-coded, but we could solved it with something like:

select COUNT(1)  from case_test
 where col_b between (select max(col_b) + 1 from case_test where COL_A <> (select col_a from (
select col_a
from case_test 
ORDER BY COL_B DESC
) where rownum = 1))
                     and
                       (select max(col_b) from case_test);


My question still remains, how can I do it with a single access to the table.
Re: Counting followed last ocurrences [message #589368 is a reply to message #589367] Thu, 04 July 2013 13:53 Go to previous messageGo to next message
Michel Cadot
Messages: 59297
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
select max(col_b) + 1


This assumes that all col_b are consecutive which is not in the prerequisite you mentioned.
COUNT(1) is silly, you don't count some 1 (why 1 and not 0 or 1024 or 'Michel'?), you count rows.

Please correctly format your query, this one is unreadable we can't see which part is related with which.
If you don't know how to do it, learn it using SQL Formatter.

Hint: you can do it with one FTS using FIRST_VALUE (or LAST_VALUE) and ROW_NUMBER.

Regards
Michel
Re: Counting followed last ocurrences [message #589393 is a reply to message #589368] Fri, 05 July 2013 03:51 Go to previous messageGo to next message
OJogador13
Messages: 9
Registered: October 2008
Junior Member
Hi, Michel

I don´t want in any way to be rude, but you seem to be missing the topic here.
The query above is not the way I want it to work, so formatting it is not my main concern.

If you know a way that could help solve the problem, please share it I´ll be very grateful.


Best Regards,
Frederico Couceiro

Re: Counting followed last ocurrences [message #589399 is a reply to message #589393] Fri, 05 July 2013 04:41 Go to previous message
Littlefoot
Messages: 19653
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OJogador13
formatting it is not my main concern

I believe it is not. However, you have to understand that unformatted queries are difficult to read and understand, so people - who are capable of answering questions - sometimes simply skip such an unformatted code and move on to another topic/question which is easier to read.

Therefore, it's mostly up to you - if you want help, you should try to follow guidelines (there aren't that many of them). If not, oh well, you might still get it, but will take some time longer.
Previous Topic: Mutating Table Error
Next Topic: Replace
Goto Forum:
  


Current Time: Thu Oct 02 10:11:12 CDT 2014

Total time taken to generate the page: 0.16180 seconds