Count of Consecutive Values in Oracle

[rd007]

How to get count of consecutive record which has the same value.
e.g.
select * from temp;

output
---------
Id Grade
1 A
2 D
3 A
4 B
5 A
6 A
7 A

i want the count of A whcih is consecutive nad latest one
my output should be

Grade Count
A 3

[Rabbit]

Left outer join the table to itself where the id is greater than the current row and the grade is not the same.

Return the minimum id from the second table. If the id is null in the second table, then return the number of rows plus one instead.

And if you subtract the current row's id from that, that will give you your answer.

[Luuk]

i can give you an example of how to do it in MySQL,
you'll have to rewrite it to Oracle-syntax yourself

select 
  c1.id, 
  c1.Grade, 
  (select count(*) from cons c4 where c4.id between c1.id and IFNULL((select min(c3.id) from cons c3 where c3.Grade<>c1.Grade and c3.id>c1.id),99999)) MAXCOUNT 
from cons c1 
ORDER BY 3 DESC  limit 1;

it outputs, with your example data:

+------+-------+----------+
| id   | Grade | MAXCOUNT |
+------+-------+----------+
|    5 | A     |        3 |
+------+-------+----------+