Order by index : Order By « Select Query « Oracle PL / SQL

1. Oracle PL / SQL
2. Select Query
3. Order By

Order by index

  

SQL>
SQL>
SQL>
SQL> create table t (
  2   item number,
  3   bin number,
  4   primary key (bin , item)
  5  );

Table created.

SQL>
SQL> insert into t values(2, 34353);

1 row created.

SQL> insert into t values(45,34353);

1 row created.

SQL> insert into t values(76,34353);

1 row created.

SQL> insert into t values(76,35667);

1 row created.

SQL> insert into t values(89,35667);

1 row created.

SQL>
SQL>
SQL> select * from t order by 1,2;

      ITEM        BIN
---------- ----------
         2      34353
        45      34353
        76      34353
        76      35667
        89      35667

5 rows selected.

SQL>
SQL> select bin, count(*), count(*)/cnt
  2  from ( select bin, count(distinct item) over () AS cnt
  3         from t
  4         where item in (2,45,76,89) )
  5  group by bin, cnt
  6  /

       BIN   COUNT(*) COUNT(*)/CNT
---------- ---------- ------------
     34353          3          .75
     35667          2           .5

2 rows selected.

SQL>
SQL>
SQL> drop table t;

Table dropped.

SQL>

   
    
  

Related examples in the same category