Removing Duplicate Rows

[tonytoney]

Ok, I admit...I haven't programmed in years. Currently I am having to write queries as a subfunction of my job. The queries are being called from inside our company transportation software which uses Delphi on the front end and Interbase on the back.

The below query is one where I am selecting data from 2 tables and comparing the 2. The query works fine but the problem is that TRACING sometimes has duplicate data resulting in more than one row with TRACEEVENT='Y'. The newest record of TRACEEVENT = 'Y' would be the best one to keep. Any guidance on how to embed another WHERE or otherwise is greatly appreciated! Here is the query:

select t.mostrecent,s.recno,s.terminal,s.reldate,s.lastfreeday,s.bolnumber,s.workorder,s.movetype,t.lineitem,t.prefix_ctnr,t.containerno,s.ingatedate,s.status,s.pickupdate,s.delvdate,s.scheddelv,s.shipper,s.scity,s.consignee,s.consigneeid,s.ccity,s.cstate,s.schedpu,s.schedputime,s.empty_notified,t.check_date,t.check_time,t.loccity,t.locstate,t.locmonth,t.locday,t.locationdate,t.locationtime,t.tracetime,t.le,t.traceevent,t.trainid,t.destev,t.destcity,t.deststate,t.destmonth,t.destday, t.reportingrr from shipments s, tracing t where (s.recno = t.recno) and (s.consigneeid = 'SAM2IL') and (t.traceevent = 'Y')

 

[bmarks]

You don't say how you know which record would be the newest record of TRACEEVENT = 'Y, but you could do it with a subquery.

You would need to create a query that selects only the records that you would want. You can then either link the result of that to shipments, or you could keep the current query and add to the where clause a statement that the record must be in the results of this new subquery.

With some more detail about the unique key on the table and how you recognize the newest record, I could be more precise on the syntax.