Duplicate Values in Query

Question

Novice SQL user here - I am trying to determine the delivery date (de_arrdate) for an order based on event data from the events table. A shipment can have multiple events, shipments usually have 4 events so the events table will return data based on shipment ID for all 4 events. Because of this, my total $$$ is overstated. How can I return only the largest value of the shipment sequence which would essentially be the final event date? My query is below. I've also attached a sample of the current output.

select dba.disp_ship.ds_id, dba.disp_ship.ds_bill_charge, 
        dba.disp_ship.ds_status, dba.disp_ship.ds_ship_type, 
        dba.disp_events.de_site, dba.disp_events.de_arrdate, 
        dba.disp_events.de_shipment_id, dba.disp_events.de_ship_seq
from dba.disp_ship
    inner join dba.disp_events on dba.disp_ship.ds_id = dba.disp_events.de_shipment_id

If you are trying to determine the delivery date, how come you end up with "my total $$$ is overstated"? :) — umberto-petrov, May 17 '21 at 16:04
Are you using MySQL or Sybase? Please don't include inappropriate tags. — Barmar, May 17 '21 at 16:06
See https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?noredirect=1&lq=1 for how to get the last event row for each shipment in MySQL. — Barmar, May 17 '21 at 16:08
There are probably similar questions for Sybase, or you could just use the same solutions as MySQL. — Barmar, May 17 '21 at 16:09
while OP has mentioned `sybase` as the database in question, we need to know which Sybase RDBMS product (ASE? IQ? SQLAnywhere? Advantage?), and preferably the version, too; the current answer makes use of a CTE but ... CTE's are not available in Sybase **ASE**, hence the need to know which Sybase RDBMS product OP is using; it would also help if OP updated the question with the criteria used to determine 'final event date', eg, is this merely the `max(event_date)` for all events, or does the query need to further limited to a specific set of 'valid' events? — markp-fuso, May 18 '21 at 15:33

score 0 · Answer 1 · answered May 17 '21 at 16:17

0

Not sure which RDBMS you are using nor the version, but if I understood correctly, you only want the amount stated in the last event of the sequence, right?

In this case, you already have the order of the events in the de_ship_seq column, so all you need to do is:

with last_event as (
  select
    de.de_arrdate, 
    de.de_shipment_id, 
    max(de.de_ship_seq)
  from dba.disp_events as de
  group by 1, 2
)
select 
  ds.ds_id,
  ds.ds_bill_charge,
  ds.de_arrdate
from dba.disp_ship as ds 
join last_event as le on ds.ds_id = le.de_shipment_id

This way, you'll not get duplicity by the table disp_events, since you're only grabbing the maximum of the sequence, which it's supposed to be the last event :)

answered May 17 '21 at 16:17

Aleix CC

1,601
1
7
18

Yes, you understand correctly. The RDBMS is Sybase. – Bertocious May 17 '21 at 16:31
So I can start the statement with "with" and enclose the statement that brings only the max de_ship_seq and then close it and continue with the rest of the statement? – Bertocious May 17 '21 at 16:32
The WITH syntax is meant to start a CTE (more info here: https://docs.microsoft.com/en-gb/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15). I like to think of CTEs as subqueries with a name, since you always give them one. In this case, in the `last_event` subquery, we're just grabbing the maximum sequence (the last event in the sequence), and its shipment_id & arr_date. Later, what we do is just a simple RIGHT JOIN between the `disp_ship` table and the CTE query, but since we already grouped by shipment_id and arr_date, we will not have duplicates :) – Aleix CC May 17 '21 at 16:58
Nice. I get it. Thank you for the link. The only join here that would be needed is for the CTE and the disp_ship table since the CTE is used to bring data from disp_events? Wouldn't I still need to join disp_events because de_arrdate is in the query? – Bertocious May 17 '21 at 17:46
Check the query inside the CTE: we're already grabbing the de_arrdate from disp_events :) – Aleix CC May 18 '21 at 06:06
I don't think the RDBMS I am using (Sybase) supports CTE. What would be the equivalent to CTE for Sybase? – Bertocious May 18 '21 at 16:11
Looks like you can, though: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.sqlanywhere.12.0.1/dbusage/ug-commontblexpr.html – Aleix CC May 19 '21 at 07:49

Gokul Kannan · Answer 2 · 2021-06-16T15:18:51.063

There are two ways to achieve this scenario.

1. Inner Query

select dba.disp_ship.ds_id, dba.disp_ship.ds_bill_charge, 
        dba.disp_ship.ds_status, dba.disp_ship.ds_ship_type, 
        dba.disp_events.de_site, dba.disp_events.de_arrdate, 
        dba.disp_events.de_shipment_id, dba.disp_events.de_ship_seq
from dba.disp_ship
    inner join dba.disp_events on dba.disp_ship.ds_id = dba.disp_events.de_shipment_id,
inner Join (Select a.de_shipment_id as shipid,max(a.de_arrdate) as arrdate 
            from disp_events a) as t on dba.disp_events.de_shipment_id = t.shipid and dba.disp_events.de_arrdate = t.arrdate

2. Procedure

//Datatype for the Temporary tables is an assumption. Replace with your data type.
begin
declare local temporary table tbl1(
   ds_id numeric(10),
   ds_bill_charge numeric(14,2), 
   ds_status int,
   ds_ship_type int, 
   de_site char(20),
   de_arrdate date, 
   de_shipment_id numeric(10),
   de_ship_seq numeric(10)
)on commit preserve rows;
declare local temporary table tbl1(
    rowid numeric(10);
    shipmentid numeric(10)
)on commit preserve rows;
declare @rowcount,@ds_id,i numeric(10);

set i = 1;
insert into tbl1
select dba.disp_ship.ds_id, dba.disp_ship.ds_bill_charge, 
        dba.disp_ship.ds_status, dba.disp_ship.ds_ship_type, 
        dba.disp_events.de_site, dba.disp_events.de_arrdate, 
        dba.disp_events.de_shipment_id, dba.disp_events.de_ship_seq
from dba.disp_ship
    inner join dba.disp_events on dba.disp_ship.ds_id = dba.disp_events.de_shipment_id;

insert into tbl2
select number(*), ds_id from(select distinct ds_id from tbl1) a;

select count(*) into @rowcount from tbl2;

while i <= @rowcount Loop
   Select ds_id into @ds_id from tbl2 where rowid = i;
   delete from tbl1 where ds_id = @ds_id and 
                de_ship_seq not in(select top 1 de_ship_seq from tbl1 a
                                   where a.ds_id = @ds_id order by de_arrdate desc);
   i++;
end Loop;
select * from tbl1;
end

Thank You...

Duplicate Values in Query

2 Answers2