Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Need help with select distinct with group by

lelandFeb 16 2009 — edited Feb 17 2009
RDBMS 10gr2

I am trying achieve having a select distinct with a order by and after looking on the internet and trying different examples, I have been unsucessful.

Here is the code working (not sorting - I wish to sort by pps.last_name however I can't seem to get it to work).
select distinct pps.last_name || ', ' ||pps.first_name || ' ' ||pps.middle_initial || '.' d, 
       emple_no r
  from cobr.vw_pps_payroll pps,
       projman pm
 where term_date is null
   and department = '0004400000'
   and pm.eid != pps.emple_no
This post has been answered by 469753 on Feb 16 2009
Jump to Answer

Comments

BluShadow
You can't know about a row being removed unless you have a record of that row somewhere, either as a copy of your old data (see example below) or you've recorded the information using delete triggers etc.
SQL> ed
Wrote file afiedt.buf

  1  with old_data as (select 1 as id, 'A' as dta from dual union all
  2                     select 2, 'B' from dual union all
  3                     select 3, 'C' from dual)
  4      ,new_data as (select 1 as id, 'A' as dta from dual union all
  5                    select 3, 'X' from dual union all
  6                    select 4, 'Y' from dual)
  7  --
  8      ,ins_upd as (select * from new_data minus select * from old_data)
  9      ,del_upd as (select * from old_data minus select * from new_data)
 10      ,upd as (select id from ins_upd intersect select id from del_upd)
 11      ,ins as (select id from ins_upd minus select id from upd)
 12      ,del as (select id from del_upd minus select id from upd)
 13  --
 14  select 'Inserted' as action, null as old_id, null as old_dta, new_data.id as new_id, new_data.dta as new_dta
 15  from new_data join ins on (ins.id = new_data.id)
 16  union all
 17  select 'Updated', old_data.id, old_data.dta, new_data.id, new_data.dta
 18  from old_data join new_data on (old_data.id = new_data.id)
 19                join upd on (upd.id = new_data.id)
 20  union all
 21  select 'Deleted', old_data.id as old_id, old_data.dta as old_dta, null as new_id, null as new_dta
 22  from old_data join del on (del.id = old_data.id)
 23  union all
 24  select 'No Change' as action, new_data.id as old_id, new_data.dta as old_dta, new_data.id as new_id, new_data.dta as new_dta
 25  from new_data where id not in (select id from ins_upd union all
 26*                                select id from del_upd)
SQL> /

ACTION        OLD_ID O     NEW_ID N
--------- ---------- - ---------- -
Inserted                        4 Y
Updated            3 C          3 X
Deleted            2 B
No Change          1 A          1 A

SQL>
1 person found this helpful
Aketi Jyuuzou
Answer
I used OLAP B-)

My OLAP articles (in Japanese)
http://www.geocities.jp/oraclesqlpuzzle/oow2009-olap-model.html
http://codezine.jp/article/corner/71
create table w_acct1 as
select 'A1' acct, 0 vers from dual union all
select 'A2' acct, 0 vers from dual union all
select 'A3' acct, 0 vers from dual union all
select 'A1' acct, 1 vers from dual union all
select 'A2' acct, 1 vers from dual union all
select 'A1' acct, 2 vers from dual union all
select 'A4' acct, 2 vers from dual;

select acct,vers,
case when appearCnt=1 and preCnt = 0 then 'null'
     when appearCnt=preCnt then 'null'
     else acct end as LD,
case when appearCnt=1 and preCnt = 0 then 'NEW'
     when appearCnt=preCnt then 'REMOVED'
     else 'NO_CHANGE' end as ADD_REMOVE
from (select a.acct,b.vers,
      count(b.acct)
      over(partition by a.acct order by b.vers) as appearCnt,
      count(b.acct)
      over(partition by a.acct
           order by b.vers rows between unbounded preceding
                                    and 1 preceding) as preCnt,
      Lag(b.acct) over(partition by a.acct order by b.vers) as LagVal
        from (select distinct acct from w_acct1) a
        Left Join w_acct1 b
      partition by (b.vers)
          on a.acct = b.acct)
 where appearCnt > preCnt
    or LagVal is not null
order by vers,acct;

AC  VERS  LD    ADD_REMOV
--  ----  ----  ---------
A1     0  null  NEW      
A2     0  null  NEW      
A3     0  null  NEW      
A1     1  A1    NO_CHANGE
A2     1  A2    NO_CHANGE
A3     1  null  REMOVED  
A1     2  A1    NO_CHANGE
A2     2  null  REMOVED  
A4     2  null  NEW
Marked as Answer by RichardSquires · Sep 27 2020
fac586
or some other cool Oracle feature I'm missing?
Workspace Manager?
1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 17 2009
Added on Feb 16 2009
5 comments
1,108 views