Home » SQL & PL/SQL » SQL & PL/SQL » Avoid UNION ALL (Oracle 11.2.0.3)
Avoid UNION ALL [message #661693] Tue, 28 March 2017 06:04 Go to next message
pointers
Messages: 439
Registered: May 2008
Senior Member
Hi,

I have a table T_SHIPMENT, we would like to get the MAX(rfrl_strt_dt) for SPP_ID IN (100,200) and
for other SPP_IDs I would like to get all the rfrl_strt_dt.

create table t_shipment (spp_id number, rfrl_strt_dt date);

begin
insert into t_shipment values (100, sysdate-356);
insert into t_shipment values (100, sysdate-350);
insert into t_shipment values (100, sysdate-200);
insert into t_shipment values (100, sysdate-100);
--
insert into t_shipment values (200, sysdate-356);
insert into t_shipment values (200, sysdate-150);
insert into t_shipment values (200, sysdate-100);
insert into t_shipment values (200, sysdate-2);
--
insert into t_shipment values (300, sysdate-1);
insert into t_shipment values (300, sysdate-10);
insert into t_shipment values (300, sysdate-15);
insert into t_shipment values (300, sysdate-20);
--
insert into t_shipment values (400, sysdate-2);
insert into t_shipment values (400, sysdate-6);
insert into t_shipment values (400, sysdate-4);
insert into t_shipment values (400, sysdate-3);
end;
/

commit;


I could achive it using UNION ALL


SELECT spp_id, MAX (rfrl_strt_dt) rfrl_strt_dt
    FROM t_shipment
   WHERE spp_id IN (100, 200)
GROUP BY spp_id
--
UNION ALL
--
SELECT spp_id, rfrl_strt_dt FROM t_shipment WHERE spp_id NOT IN (100, 200);

I see the table is accessed twice, could we achieve this using a single table, I tried to do it using analytics without luck.

Thank you in advance.

Regards,
Poiners
Re: Avoid UNION ALL [message #661694 is a reply to message #661693] Tue, 28 March 2017 06:33 Go to previous messageGo to next message
cookiemonster
Messages: 13014
Registered: September 2008
Location: Rainy Manchester
Senior Member
You need to use row_number to skip the rows for 100,200 you're not interested and case to determine whether to use max or the raw data:
SQL> select spp_id, case when spp_id in (100,200) then maxt else rfrl_strt_dt end as rfrl_strt_dt
  2  from (
  3  select spp_id, max(rfrl_strt_dt) over (partition by spp_id) as maxt,
  4         rfrl_strt_dt, row_number() over(partition by spp_id order by rfrl_strt_dt) as rn
  5  from t_shipment)
  6  where spp_id not in (100,200) or rn = 1;
 
    SPP_ID RFRL_STRT_DT
---------- ------------
       100 12/18/2016 1
       200 03/26/2017 1
       300 03/08/2017 1
       300 03/13/2017 1
       300 03/18/2017 1
       300 03/27/2017 1
       400 03/22/2017 1
       400 03/24/2017 1
       400 03/25/2017 1
       400 03/26/2017 1
 
10 rows selected
Re: Avoid UNION ALL [message #661702 is a reply to message #661693] Tue, 28 March 2017 12:58 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2704
Registered: January 2010
Location: Connecticut, USA
Senior Member
Just for fun - grouping sets solution:

select  spp_id,
        case grouping(rfrl_strt_dt)
          when 0 then rfrl_strt_dt
          else max( rfrl_strt_dt)
        end rfrl_strt_dt
  from  t_shipment
  group by grouping sets((spp_id),(spp_id,rfrl_strt_dt,rowid))
  having grouping(rfrl_strt_dt) = case
                                    when spp_id in (100,200) then 1
                                    else 0
                                  end
/

    SPP_ID RFRL_STRT
---------- ---------
       100 18-DEC-16
       200 26-MAR-17
       300 08-MAR-17
       300 13-MAR-17
       300 18-MAR-17
       300 27-MAR-17
       400 22-MAR-17
       400 24-MAR-17
       400 25-MAR-17
       400 26-MAR-17

10 rows selected.

SQL> 

SY.
Re: Avoid UNION ALL [message #661703 is a reply to message #661702] Tue, 28 March 2017 14:27 Go to previous messageGo to next message
pointers
Messages: 439
Registered: May 2008
Senior Member
Thank you Cookiemonster and Solomon for showing us different ways of doing it.
Now, I learnt these two methods.

Regards
Pointers
Re: Avoid UNION ALL [message #661704 is a reply to message #661703] Tue, 28 March 2017 14:51 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8805
Registered: November 2002
Location: California, USA
Senior Member
One more way, not necessarily any better or worse.

SCOTT@orcl_12.1.0.2.0> SELECT DISTINCT spp_id,
  2  	    CASE WHEN spp_id IN (100, 200)
  3  		 THEN MAX(rfrl_strt_dt) OVER (PARTITION BY spp_id)
  4  		 ELSE rfrl_strt_dt
  5  	    END rfrl_strt_dt
  6  FROM   t_shipment
  7  ORDER  BY 1, 2
  8  /

    SPP_ID RFRL_STRT_DT
---------- ---------------
       100 Sun 18-Dec-2016
       200 Sun 26-Mar-2017
       300 Wed 08-Mar-2017
       300 Mon 13-Mar-2017
       300 Sat 18-Mar-2017
       300 Mon 27-Mar-2017
       400 Wed 22-Mar-2017
       400 Fri 24-Mar-2017
       400 Sat 25-Mar-2017
       400 Sun 26-Mar-2017

10 rows selected.
Re: Avoid UNION ALL [message #661720 is a reply to message #661704] Wed, 29 March 2017 08:14 Go to previous message
pointers
Messages: 439
Registered: May 2008
Senior Member
Laughing very simple and elegant.

I don't know why it was not in my thoughts

Regards,
Pointers
Previous Topic: Grouping for varying weeks
Next Topic: UTL_FILE_DIR
Goto Forum:
  


Current Time: Fri Feb 23 02:50:48 CST 2018

Total time taken to generate the page: 0.01590 seconds