Re: Selecting distinct records
Posted by:
Sean Nolan
Date: April 06, 2005 10:11AM
The solution here depends on what version of MySql you're using. Your join is based on the jobno, but you want to make the results distinct on the waybillno, I have assumed that there is one waybillno per jobno, otherwise you'll have to put the waybillno into the waytrack table too.
With version 4.1.x and higher, it's easier because you can use a subquery like this:
SELECT
wb.jobno, wb.waybillno, wb.dadd3, wb.shipdate, wt1.status, wt1.date, wt1.time
FROM waybill wb, waytrack wt1
WHERE acctno = 'ROBIN'
AND waybilltype='C'
AND wb.jobno = wt1.jobno
AND ADDTIME(CAST(wt1.date AS DATETIME), wt1.time) = (SELECT MAX(ADDTIME(CAST(wt2.date AS DATETIME), wt2.time)) FROM waytrack wt2 WHERE wt2.jobno = wb.jobno)
ORDER BY wb.shipdate DESC
If you're using a version earlier than 4.1.x subqueries are not supported so you have to put all the subquery results in a temporary table first. Also the ADDTIME function was added in 4.1, so we can't use that either.
CREATE TEMPORARY TABLE TempWt
SELECT wt.jobno, MAX(CAST(CONCAT(wt.date, wt.time) AS DATETIME)) AS TrackDT
FROM waytrack wt, waybill wb
WHERE acctno = 'ROBIN'
AND waybilltype='C'
AND wb.jobno = wt.jobno
GROUP BY wt.jobno;
SELECT
wb.jobno, wb.waybillno, wb.dadd3, wb.shipdate, wt.status, wt.date, wt.time
FROM waybill wb, waytrack wt, TempWT twt
WHERE acctno = 'ROBIN'
AND waybilltype='C'
AND wb.jobno = wt.jobno
AND twt.jobno = wb.jobno
AND CAST(CONCAT(wt.date, wt.time) AS DATETIME) = twt.TrackDT
ORDER BY wb.shipdate DESC;
DROP TABLE TempWt;
I didn't actually run these so forgive me if I made a typo in either of them! :-)