Ardahan Kisbet Ardahan Kisbet - 5 months ago 26
SQL Question

How to group rows after another grouping in oracle?

I have a table called correctObjects. In this tablet here a lot of grups which has different number records. One example is given below as grup 544 has 5 rows in table. So firstly, I should group all records by GRUP COLUMN then I must do inner matching by CAP COLUMN. So in grup#544 there is three different CAP values then I must give Inner Group number to these records. How can I do these two level grouping process. GRUP column is already done. Inner Grup Column is null in every records.
enter image description here

After Inner Group process, It must look like as belows:
enter image description here

I am using Oracle 11g R2 and PL/SQL Developer


Your question lacks certain details, so I'll just give you a starting point, and you can tweak it to suit your needs.

It's not entirely clear, but the way I understand it, you want to rank the different rows by cap. And I think the ranking is independent for every distinct grup value.

What's not clear to me is why 125 mm is ranked 1, and 62 mm is ranked 2. Is it based on the value? Is it based on which row is the first one, and if so, how are the rows ordered? Or maybe you don't really care which one is first or second, as long as they are grouped correctly. I'll have to assume the latter.

In any case, it sounds like you want to use the dense_rank() analytic function in some form:

select mip, startmi, cap, grup,
       dense_rank() over (partition by grup order by cap) as inner_grup
  from tbl