The question isn't relevant (that is my point).

Look -- if you NEED something grouped by X,Y,Z it really just doesn't matter AT ALL if it would be faster to group by A, B does it? It isn't what you need. It is not like "grouping by a primary key is an ALTERNATIVE". When tuning, you are looking for same result alternatives -- this just isn't one of them!

(as an aside, there is perhaps an advantage to getting the first rows back fastest when the group by columns are indexed -- which will be the case in a primary key situation. That does not mean "index all group by columns"!!! Not even a little)


As for the "when/how" -- the answer is "it depends". It depends on the predicate, the table(s) involved, the lack or presence of indices and so on.

Sometimes after all data, sometimes as soon as the first group is known to be done.


GROUPING is logically done first.
Then Analytics.
And finally sorting.

We can see this with this query:

scott@ORA920.US.ORACLE.COM> select deptno, max(sal), lag(deptno) over ( order by deptno )
2 from emp
3 group by deptno
4 order by 2
5 /

DEPTNO MAX(SAL) LAG(DEPTNO)OVER(ORDERBYDEPTNO)
------ ---------- ------------------------------
30 2850 20
20 3000 10
10 5000

scott@ORA920.US.ORACLE.COM>

Now, the analytics can only work on group by columns in this case (when you have an aggregate, analytics can only "see" the aggregated columns, this perhaps shows that even better:
scott@ORA920.US.ORACLE.COM> select deptno,
2 max(sal),
3 lag(deptno) over ( order by deptno ),
4 sum(max(sal)) over ()
5 from emp
6 group by deptno
7 order by 2
8 /

DEPTNO MAX(SAL) LAG(DEPTNO)OVER(ORDERBYDEPTNO) SUM(MAX(SAL))OVER()
------ ---------- ------------------------------ -------------------
30 2850 20 10850
20 3000 10 10850
10 5000 10850

scott@ORA920.US.ORACLE.COM>


we can sum() over() the max(sal) -- meaning the analytic MUST be done after the grouping has already taken place.

The order by is always done dead last.

sorry -- not following you on this one.

If you are asking "will the above work and work correctly", yes -- it will. it will group by the decode.

if you don't like the double decode coding


select deptno, count(*), thedate
from ( select deptno, decode(emp_status, 'ACTIVE', hiredate, firedate) thedate
from emp
where .... )
group by thedate

If the index was on city, country -- you would have no need to add the extra and most like very confusing group by column.

tell me -- do you want distinct rows -- is that your goal? then by all means use distinct.

tell me -- do you want to group by, to perform aggregates? then by all means use group by (as nothing else will do)

Use the one that has more semantic meaning so that 2 years from now after you've moved on -- it is more clear to the person inheriting your code what you meant.

the answer is to use distinct.

the optimizer knows what to do in both cases. but only distinct is semantically correct.

"Like i need to select product_code,dealer_code as grouped columns and distinct
shipment code to be compared within the inner loop."

makes no sense whatsoever to me.

I fail to see what "duplicate" entries you could be getting from the query with distinct.

Tell me this -- if there is more then one shipment_code for each product_code, dealer_code -- which "random" shipment_code do you want???

You'll need to use an example here with data -- not getting your point.

if A is the primary key -- i propose this


SELECT
a,
name,
salary,
flg,
address1,
address2
FROM
tab1



done -- same answer, less work. if you group by primary key -- you get exactly the same thing as if you DO NOT group by

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t1 as select * from all_objects;

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t2 as select * from all_objects;

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert /*+ append */ into t2 select * from t2;

23655 rows created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;

Commit complete.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert /*+ append */ into t2 select * from t2;

47310 rows created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;

Commit complete.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert /*+ append */ into t2 select * from t2;

94620 rows created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;

Commit complete.

ops$tkyte@ORA817DEV.US.ORACLE.COM> update t2 set object_name = rownum;

189240 rows updated.

ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;

Commit complete.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t1 add constraint t1_pk primary key(object_id);

Table altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t2 add constraint t2_pk primary key(object_id,object_name);

Table altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table t1 compute statistics
  2  for table
  3  for all indexes
  4  for all indexed columns;

Table analyzed.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table t2 compute statistics
  2  for table
  3  for all indexes
  4  for all indexed columns;

Table analyzed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace traceonly
ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.object_id, a.object_name, a.owner, a.created, sum(b.data_object_id)
  2    from t1 a, t2 b
  3   where a.object_id = b.object_id
  4   group by a.object_id, a.object_name, a.owner, a.created
  5  /

23654 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4467 Card=23654 Bytes=1513856)
   1    0   SORT (GROUP BY) (Cost=4467 Card=23654 Bytes=1513856)
   2    1     MERGE JOIN (Cost=1382 Card=189232 Bytes=12110848)
   3    2       SORT (JOIN) (Cost=203 Card=23654 Bytes=1111738)
   4    3         TABLE ACCESS (FULL) OF 'T1' (Cost=10 Card=23654 Bytes=1111738)
   5    2       SORT (JOIN) (Cost=1179 Card=189240 Bytes=3217080)
   6    5         TABLE ACCESS (FULL) OF 'T2' (Cost=75 Card=189240 Bytes=3217080)




Statistics
----------------------------------------------------------
          0  recursive calls
         49  db block gets
       2934  consistent gets
        977  physical reads
          0  redo size
    1778152  bytes sent via SQL*Net to client
     175361  bytes received via SQL*Net from client
       1578  SQL*Net roundtrips to/from client
          0  sorts (memory)
          3  sorts (disk)
      23654  rows processed

ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.object_id, max(a.object_name), max(a.owner), max(a.created), sum(b.data_object_id)
  2    from t1 a, t2 b
  3   where a.object_id = b.object_id
  4   group by a.object_id
  5  /

23654 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1382 Card=23654 Bytes=1513856)
   1    0   SORT (GROUP BY NOSORT) (Cost=1382 Card=23654 Bytes=1513856)
   2    1     MERGE JOIN (Cost=1382 Card=189232 Bytes=12110848)
   3    2       SORT (JOIN) (Cost=203 Card=23654 Bytes=1111738)
   4    3         TABLE ACCESS (FULL) OF 'T1' (Cost=10 Card=23654 Bytes=1111738)
   5    2       SORT (JOIN) (Cost=1179 Card=189240 Bytes=3217080)
   6    5         TABLE ACCESS (FULL) OF 'T2' (Cost=75 Card=189240 Bytes=3217080)




Statistics
----------------------------------------------------------
          0  recursive calls
         47  db block gets
       2934  consistent gets
        734  physical reads
          0  redo size
    1778173  bytes sent via SQL*Net to client
     175361  bytes received via SQL*Net from client
       1578  SQL*Net roundtrips to/from client
          2  sorts (memory)
          2  sorts (disk)
      23654  rows processed

ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace off


<b>that shows they use the same memory but differing amounts of temp in different ways (sorts memory/disk counts are different).

I suggest you stick with the way you have (group by c1, c2, c3, c4, ... ) instead of applying MAX()

reasoning:  it is semantically more meaningful to the developers looking at your query 3 years from now.</b>

 

group by trunc(date_column-7,'mm')

looks like you want to write a report then -- in master/detail style.

great, so write a report -- not everything is doable in a single query every time without some procedural workings on it.

benchmark it.

in either case, it'll only take a couple of seconds/minutes to retrieve both result sets so you can easily test both and see what happens. Just


alter session set timed_statistics=true;
alter session set sql_trace=true;
set arraysize 100
set termout off
run query 1
run query 2
set termout on
exit


run that in plus and then use tkprof

ops$tkyte@ORA9IR2> select min(d1), max(d2), v
  2    from (
  3  select d1, d2, v, max(rn) over ( order by d1 ) max_rn
  4    from (
  5  select t.*,
  6         case when (lag(v) over (order by d1) <> v)
  7              then row_number() over (order by d1)
  8          end rn
  9    from t
 10         )
 11         )
 12   group by v, max_rn
 13   order by 1
 14  /
 
MIN(D1)   MAX(D2)            V
--------- --------- ----------
15-NOV-03 30-NOV-03          1
30-NOV-03 04-DEC-03          2
04-DEC-03 30-DEC-03          1
30-DEC-03 18-FEB-04          3
 

run the inline views one by one by one -- wrapping the innermost with the next layer and so on to see what I did. 

it'll be a function of your SORT_AREA_SIZE if you are using 8i and before -- pga_aggregate_target | sort_area_size in 9i and up

so -- what are you (version)
and what are they (init.oras)

while this does not show "2 hours" (a TKPROF would be really really handy) it does show that sort area size can affect this greatly


big_table@ORA9IR2> alter session set workarea_size_policy = manual;

Session altered.

big_table@ORA9IR2> alter session set sort_area_size = 65536;

Session altered.

big_table@ORA9IR2> set arraysize 100
big_table@ORA9IR2> set timing on
big_table@ORA9IR2> set autotrace traceonly
big_table@ORA9IR2> select * from big_table where rownum <= 350000;

350000 rows selected.

Elapsed: 00:00:04.91

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1379 Card=350000 Bytes=35000000)
1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (FULL) OF 'BIG_TABLE' (Cost=1379 Card=999620 Bytes=99962000)




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8469 consistent gets
4456 physical reads
0 redo size
24242394 bytes sent via SQL*Net to client
38988 bytes received via SQL*Net from client
3501 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
350000 rows processed

big_table@ORA9IR2> select id, object_name, owner, count(*), avg(data_object_id), min(last_ddl_time), max(created)
2 from (select * from big_table where rownum <= 350000 )
3 group by id, object_name, owner;

350000 rows selected.

Elapsed: 00:00:43.40

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=15844 Card=350000 Bytes=20650000)
1 0 SORT (GROUP BY) (Cost=15844 Card=350000 Bytes=20650000)
2 1 VIEW (Cost=1379 Card=350000 Bytes=20650000)
3 2 COUNT (STOPKEY)
4 3 TABLE ACCESS (FULL) OF 'BIG_TABLE' (Cost=1379 Card=999620 Bytes=58977580)




Statistics
----------------------------------------------------------
0 recursive calls
689 db block gets
5021 consistent gets
22901 physical reads
0 redo size
16534945 bytes sent via SQL*Net to client
38988 bytes received via SQL*Net from client
3501 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
350000 rows processed

big_table@ORA9IR2> alter session set sort_area_size = 6553600;

Session altered.

Elapsed: 00:00:00.01
big_table@ORA9IR2> select id, object_name, owner, count(*), avg(data_object_id), min(last_ddl_time), max(created)
2 from (select * from big_table where rownum <= 350000 )
3 group by id, object_name, owner;

350000 rows selected.

Elapsed: 00:00:05.05

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4755 Card=350000 Bytes=20650000)
1 0 SORT (GROUP BY) (Cost=4755 Card=350000 Bytes=20650000)
2 1 VIEW (Cost=1379 Card=350000 Bytes=20650000)
3 2 COUNT (STOPKEY)
4 3 TABLE ACCESS (FULL) OF 'BIG_TABLE' (Cost=1379 Card=999620 Bytes=58977580)




Statistics
----------------------------------------------------------
0 recursive calls
6 db block gets
5021 consistent gets
8080 physical reads
0 redo size
16534945 bytes sent via SQL*Net to client
38988 bytes received via SQL*Net from client
3501 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
350000 rows processed

big_table@ORA9IR2> set autotrace off
big_table@ORA9IR2> set timing off
big_table@ORA9IR2>

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96536/ch44.htm <code>

1) $ oerr ora 1792
01792, 00000, "maximum number of columns in a table or view is 1000"



ops$tkyte@ORA920PC> declare
  2          l_stmt long := 'create or replace view v as select 1 c1';
  3  begin
  4          for i in 2 .. 2000
  5          loop
  6                  l_stmt := l_stmt || ',1 c'||i;
  7          end loop;
  8          l_stmt := l_stmt || ' from dual';
  9          execute immediate l_stmt;
 10  end;
 11  /
declare
*
ERROR at line 1:
ORA-01792: maximum number of columns in a table or view is 1000
ORA-06512: at line 9


2) unlimited in theory, limited by practical limits
3) same as #2
4) same as #2
5) same as #2
6) unlimited?  not sure what you mean



ops$tkyte@ORA920PC> variable x refcursor
ops$tkyte@ORA920PC> declare
  2          l_stmt long := 'select 1 c1';
  3  begin
  4          for i in 2 .. 2000
  5          loop
  6                  l_stmt := l_stmt || ',1 c'||i;
  7          end loop;
  8          l_stmt := l_stmt || ' from dual';
  9          open :x for l_stmt;
 10  end;
 11  /
 
PL/SQL procedure successfully completed.
 
 
ops$tkyte@ORA920PC> print x

        C1         C2         C3         C4         C5         C6         C7         C8         C9        C10        C11       C12
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
...............
 

I'll guess, are you running this in toad or something.

ok, set autotrace traceonly explain and run both queries, lets see the plans

(and you did retrieve the ENTIRE result set in plus right -- you are not just timing the time to get the first rows back correct?)

distinct -- think about it. in order to DISTINCT a set of rows - you need to get *every row*. And then you can distinct. (in general, an index on every column being distincted could change that)

in order to show an ordered set -- maybe we just need to read data via an index. you could get the first row hours/days before the last row. (group by, same thing, you group by some indexed columns -- an index can be used to rapidly return the first group)

for the distinct it would probably use the index as a skinnier version of the table, fast full scan and return the distincted values.  for a group by - it would either do that as well, or it might well use the index in an index range scan (single block io) to return first rows fastest.

different data might have different results.


ops$tkyte@ORA9IR2> create table t ( a int, b int, c int, d int, e int, f char(80),
  2  primary key(a,b,c,d,e) );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_stats.set_table_stats(user,'T',numrows=>1000000,numblks=>1000000);
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select distinct a,b,c from t;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6865 Card=1000000 Bytes=39000000)
   1    0   SORT (UNIQUE) (Cost=6865 Card=1000000 Bytes=39000000)
   2    1     INDEX (FAST FULL SCAN) OF 'SYS_C005303' (UNIQUE) (Cost=4 Card=1000000 Bytes=39000000)
 
 
 
ops$tkyte@ORA9IR2> select /*+ FIRST_ROWS */ distinct a,b,c from t;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=6865 Card=1000000 Bytes=39000000)
   1    0   SORT (UNIQUE) (Cost=6865 Card=1000000 Bytes=39000000)
   2    1     INDEX (FAST FULL SCAN) OF 'SYS_C005303' (UNIQUE) (Cost=4 Card=1000000 Bytes=39000000)
 
 
 
ops$tkyte@ORA9IR2> select a,b,c from t group by a,b,c;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=1000000 Bytes=39000000)
   1    0   SORT (GROUP BY NOSORT) (Cost=26 Card=1000000 Bytes=39000000)
   2    1     INDEX (FULL SCAN) OF 'SYS_C005303' (UNIQUE) (Cost=26 Card=1000000 Bytes=39000000)
 
 
 
ops$tkyte@ORA9IR2> select /*+ FIRST_ROWS */ a,b,c from t group by a,b,c;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=26 Card=1000000 Bytes=39000000)
   1    0   SORT (GROUP BY NOSORT) (Cost=26 Card=1000000 Bytes=39000000)
   2    1     INDEX (FULL SCAN) OF 'SYS_C005303' (UNIQUE) (Cost=26 Card=1000000 Bytes=39000000)
 
 
 
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2>
 

if an employess can have >1 row with "unique data"

which "row" would be returned in your world?

you can:

select *
from (select emp.*,
row_number() over (partition by empno order by <something>) rn
from emp )
where rn = 1;

use something to decide "which row is first for an empno")

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:3027089372477 <code>

same issue.

select *
from ( select rollnumber, subject, count(*) over (partition by subject) cnt
from t )
where cnt > 1


that is just psuedo code -- as you didn't give me a create table, or insert into statements. the crux of the answer is there, but the sql is untested....


scott@ORA9IR2> select empno, job from emp order by job;

EMPNO JOB
---------- ---------
7788 ANALYST
7902 ANALYST
7369 CLERK
7876 CLERK
7934 CLERK
7900 CLERK
7566 MANAGER
7782 MANAGER
7698 MANAGER
7839 PRESIDENT
7499 SALESMAN
7654 SALESMAN
7844 SALESMAN
7521 SALESMAN

14 rows selected.

scott@ORA9IR2> select *
2 from ( select empno, job, count(*) over (partition by job) cnt
3 from emp )
4 where cnt > 1;

EMPNO JOB CNT
---------- --------- ----------
7788 ANALYST 2
7902 ANALYST 2
7369 CLERK 4
7876 CLERK 4
7934 CLERK 4
7900 CLERK 4
7566 MANAGER 3
7782 MANAGER 3
7698 MANAGER 3
7499 SALESMAN 4
7654 SALESMAN 4
7844 SALESMAN 4
7521 SALESMAN 4

13 rows selected.

scott@ORA9IR2> break on deptno
scott@ORA9IR2> select deptno, ename from emp order by deptno;

DEPTNO ENAME
---------- ----------
10 CLARK
KING
MILLER
20 SMITH
ADAMS
FORD
SCOTT
JONES
30 ALLEN
BLAKE
MARTIN
JAMES
TURNER
WARD

14 rows selected.

yes, break is a sqlplus reporting format.

you could use row_number() and decode if you really wanted, but the tool that prints this out would be "best able to surpress" this


1 select decode(row_number() over (partition by deptno order by ename),1,deptno) deptno_fmt, ename
2 from emp
3* order by deptno, ename
scott@ORA9IR2> /

DEPTNO_FMT ENAME
---------- ----------
10 CLARK
KING
MILLER
20 ADAMS
FORD
JONES
SCOTT
SMITH
30 ALLEN
BLAKE
JAMES
MARTIN
TURNER
WARD

14 rows selected.

select distinct ... group by ....
and
select .... group by .....

can return different results


ops$tkyte@ORA9IR2> create table t ( x int, y int, z int );
Table created.
 
ops$tkyte@ORA9IR2> insert into t values ( 1, 1, 1 );
1 row created.
 
ops$tkyte@ORA9IR2> insert into t values ( 1, 2, 1 );
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select x, sum(z)
  2    from t
  3   group by x, y
  4  /
 
         X     SUM(Z)
---------- ----------
         1          1
         1          1
 
ops$tkyte@ORA9IR2> select distinct x, sum(z)
  2    from t
  3   group by x, y
  4  /
 
         X     SUM(Z)
---------- ----------
         1          1
 
ops$tkyte@ORA9IR2>
 

of course it does -- think about it.

for a given:

GROUP BY ptcd.region, ptcd.phone, addr1, addr2, addr3, addr4, addr5

you have two type||natures:

AND TYPE || nature IN ('NA', 'NL')


So, which one do you want? Either of them? if so, just take the max(type||nature), if you say "don't know which one I want", then neither do I.


if you want "both", if they exist,


max( decode( type||nature, 'NA', 'NA' ) ) type_nature_1,
max( decode( type||nature, 'NL', 'NL' ) ) type_nature_2,


if there is an NA, the _1 would be filled it for that grouping, if there is an NL -- _2 would be. If one or the other is not there for a grouping, it would show "null"

... But, we didn't know that any column that is there in the ORDER BY clause but not in the SELECT list should also be there in the GROUP BY clause of a query. We guess, nothing like this is mentioned in the documentation. ....


it is not obvious?


If

a) the column to be order by is not an aggregate function
b) grouped by

HOW could it be in the result set to be ordered by????!?!?!?!


think about this please.

You have columns A, B, C

You

select, a, sum(b) from t group by a;


HOW can you possibly order that result set by C??!?!?!?!?! it makes no sense.

A     B       C
----  -----   ------
1     0       1
1     0       2
2     0       2
2     0       3

Ok, using that set of data, grouped by A, sum of B, HOW DO YOU 'ORDER' by C??!?

and what is the "desired order", give us a "for example"

ok, tell me, what is the sorted order of:

SQL> select distinct title from t_videos order by rented_date desc;

if the input data is:

TITLE_ID TITLE RENTED_DA
---------- -------------------- ---------
4 TITANIC 07-MAR-07
1 TITANIC 06-MAR-07
5 MEN IN BLACK 07-MAR-07
3 MEN IN BLACK 06-MAR-07


Ok, tell me - which comes first, and why.


so, maybe you really mean something like:

select title, MAX(rented_date)
  from t
 group by title
 order by max(rented_date);

you need to - well - sort of identify the DATE you want to sort with.


you need to

a) build a result set that has all of the information - including the date
b) order by that date.

and I'll keep asking the same tired question over and over.


how can you possibly order by this column - if it makes the results "not distinct" that means for sure there is MORE THAN ONE VALUE of trans_unique associated with each grouped record.

WHICH ONE OF THOSE TRANS_UNIQUE values do you want to use?

do you want the max? use max then
do you want the min? use min then
do you want the avg? the stddev? the XXXX?? use that aggregate then.


You have to PICK the trans_unique value you want associated with each grouped set of records - and then - and THEN - you may order by it.

think about this please.

POLICY_NUMBER        TRANS_TYPE       TRANS_DATE        TRANS_UNIQUE
---------------      --------------   -------------     ----------------
123                  X                01-jan-2007       1
456                  X                01-jan-2007       2
123                  X                01-jan-2007       3
456                  X                01-jan-2007       4

ok, now what??? the distinct values are:


123, x, 01-jan-2007
456, x, 01-jan-2007

Now, please tell me which comes "first" and "why" - which trans_unique (each grouped set had TWO TRANS_UNIQUE in this case) is used to sort.

Once you answer that - your ultimate answer will be obvious!!!

you probably meant order by "trans_unique DESC" (from big to small) in the second query.

the order by in the inline view (not the one in the analytic function, that is needed) is not necessary or useful.

and you need to KEEP the trans_unique in the second query so you can actually sort the result set by it after selecting out the rnum = 1 values...

select a, b, c
  from t
 group by a, b, c
 order by max(d)

will return the same results as (assuming that D is unique by A,B,C)

select a, b, c
  from (select a, b, c, d, 
               row_number() over (partition by a,b,c order by d DESC)rn
          from t
       )
 where rn = 1
 order by d

but the group by approach is likely the most sensible.

see above, your query is wrong, the order by must be on the OUTER query, not in the inline view.

ahh, the infamous EAV model (entity-attribute-value)

funny - you are using it, but you don't know how to query it.


search this site for the word pivot to see examples of pivoting a result set.

I would strongly encourage you to not do that, do not use to_char to truncate a date, use trunc

trunc(deldate,'mm')


it keeps things sortable
it doesn't use tons of cpu to format a date over and over and over

select to_char(the_date,'yyyymm'), cnt
  from (select trunc(deldate,'mm') the_date, count(*) cnt
          from t@r
         group by trunc(deldate,'mm')
       )
order by the_date
/

I cannot reproduce, but try my query and see what happens:

ops$tkyte%ORA11GR2> select TO_CHAR(DELDATE, 'YYYYMM'), COUNT(*)
  2    FROM t@loopback@ora11gr2
  3   GROUP BY TO_CHAR(DELDATE, 'YYYYMM');

TO_CHA   COUNT(*)
------ ----------
201206          1

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select to_char(the_date,'yyyymm'), cnt
  2    from (select trunc(deldate,'mm') the_date, count(*) cnt
  3            from t@loopback@ora11gr2
  4           group by trunc(deldate,'mm')
  5         )
  6  order by the_date
  7  /

TO_CHA        CNT
------ ----------
201206          1

the second one will be more performant in general as well.


It could have been an issue in 11.2.0.2 that was resolved in 11.2.0.3

you can try using analytics

select * 
  from (
select child.*, 
       row_number() over (partition by time_building_block_id 
                          order by object_version_number DESC) rn 
  from hxc_time_building_blocks child
       )
 where rn = 1

Call that query Q, you could then:

SELECT child.resource_id Person_id,
               TRUNC (child.start_time) Start_Date,
               TRUNC (child.stop_time) Stop_Date,
               SUM (1) N,
               MIN (child.start_time) Earliest_In,
               MAX (child.stop_time) Latest_Out,
               SUM(TO_CHAR (child.stop_time, 'HH24') * 60
                   + DECODE (TRUNC (child.stop_time),
                        TRUNC (child.start_time), 0,
                        1440)
                   + TO_CHAR (child.stop_time, 'MI')
                   - TO_CHAR (child.start_time, 'HH24') * 60
                   - TO_CHAR (child.start_time, 'MI'))
                  W
          FROM (Q) child
        GROUP BY child.resource_id,
                 TRUNC (child.start_time),
                 TRUNC (child.stop_time)

ops$tkyte%ORA11GR2> select max(id) keep ( dense_rank first order by a desc ) id,
  2         max(a) a,
  3         b,
  4         max(c) keep (dense_rank last order by a ) c,
  5         max(d) keep (dense_rank last order by a ) d,
  6         max(e) keep (dense_rank last order by a ) e,
  7         max(f) keep (dense_rank last order by a ) f
  8    from testgroup
  9    group by b
 10    order by 1
 11  /

        ID          A          B C     D     E     F
---------- ---------- ---------- ----- ----- ----- -----
         2          3          2 c2    d2    e2    f2
         5          4          3 c5    d5    e5    f5
         7          6          4 c7    d7    e7    f7

that works if A is unique within B. As you can imagine, if A is repeated multiple times for a given B value, your question doesn't make sense.

http://docs.oracle.com/cd/E14072_01/server.112/e10592/functions063.htm