sql - row_number or rank similar data -

i have table this:

col1    col2        a        a        f b        b b        b b        h c        l        a        a        a        e c        c c        c c        c c        c c        c c        j 

and want result this:

col1     count         3 b         3 c         1         4 c         6 

if col1 <> col2 reset count... want sql code not pl-sql etc.

maybe row_number() over(reset when col1<>col2).

please me.

ok freinds thank you. sorry bad english.

in fact table :

id      col1    col2 1000            2000            3000            f 4000     b        b 5000     b        b 6000     b        h 7000     c        l 8000            9000            10000           11000           e 12000    c        c 13000    c        c 14000    c        c 15000    c        c 16000    c        c 17000    c        j 

id column unique , has ordered values always. maybe solve problem. sorry missing information you. , want solution above.

i want col1 , count. not col1 unique, count must 1,2,3 bla bla bla... until col1 <> col2... after row count must reset.

first, i'd note, without having order by clause, cannot guarantee order of results. sort of calculation, useful have identity (auto-incremental) field establish order.

that said, can attempt use row_number() create field order on.

with yourtablewithrn (   select col1, col2, row_number() on (order (select null)) rn   yourtable   ),  yourtablegrouped (   select *,     rn - row_number() on (partition col1 order rn) grp   yourtablewithrn ) select col1,        count(col2) cnt yourtablegrouped group col1, grp order min(rn) 

• sql fiddle demo