MySQL Forums
Forum List  »  Newbie

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! :-)

Options: ReplyQuote


Subject
Written By
Posted
April 06, 2005 09:07AM
Re: Selecting distinct records
April 06, 2005 10:11AM
April 06, 2005 02:15PM
April 06, 2005 02:43PM
April 06, 2005 03:40PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.