2 Replies Latest reply: Oct 8, 2012 4:49 AM by Ayham RSS

    SQL - sorting and grouping.

    Ayham
      Hi ,

      i have question posted from two days but no body help ..
      this link

      Ordering according comparing - SQL Experts pls help

      regards

      Edited by: Ayham on Oct 8, 2012 2:54 AM
        • 1. Re: SQL - sorting and grouping.
          jeneesh
          If I am clear..
          SQL> with t as
            2  (select percentage,loc1,loc2,sum(case when percentage = 0 then 1
            3                     when loc1 in (l1,l2) then 0
            4                 when loc2 in (l1,l2) then 0
            5                 when l1 is null and l2 is null then 0
            6                 else 1
            7            end) over(order by rn) sm
            8  from (     select id,location,percentage,
            9                     regexp_substr(location,'\d+',1,1) LOC1,
           10                     regexp_substr(location,'\d+',1,2)  LOC2,
           11                     lag(regexp_substr(location,'\d+',1,1))
           12                     over(order by percentage desc) l1,
           13                     lag(regexp_substr(location,'\d+',1,2))
           14                     over(order by percentage desc) l2,
           15             row_number() over(order by percentage desc) rn
           16     from temp_value
           17     order by percentage desc
           18       )
           19  )
           20  select loc,min(sm)+1 grp
           21    from(
           22      select loc,rownum rn,sm
           23      from(
           24      select percentage,decode(rn,1,loc1,loc2) loc,sm
           25      from t a,
           26           (select 1 rn from dual union all
           27            select 2 from dual ) b
           28      order by percentage desc,decode(rn,1,loc1,loc2) asc
           29     )
           30  )
           31   group by loc
           32  order by min(sm),min(rn);
          
          LOC                         GRP
          -------------------- ----------
          4                             1
          5                             1
          2                             1
          1                             1
          6                             2
          3                             3
          
          6 rows selected.
          • 2. Re: SQL - sorting and grouping.
            Ayham
            Dear jeneesh ,

            Really really i am grateful for you.
            Many thanks.

            The correct answer from you.
            my regards