On Wed, 8 Nov 2006, Grant Parnell ELX wrote:
select
itmmaster.id,
suppstock.stockavail,
suppstock.lastupdated
from
itmmaster
left join suppstock on itmmaster.id = suppstock.id
where
-- match only latest suppstock entry
suppstock.lastupdated = (select s2.lastupdated from suppstock s2
where s2.id = suppstock.id order by lastupdated desc limit 1)
-- match only entries with stock
and suppstock.stockavail>0;
You could probably also try max(s2.lastupdated) in the subquery instead of
an ordered select with limit.
I was just getting excited about your above suggestion but realised the
sub-query's just going to select literally the most recent log entry where I
really want the most recent log entries for all products. It'd work if I
could add to the subquery "where [EMAIL PROTECTED]" where @variable gets set by
the main select. This depends on the internal order of processing and I'll
check that out.
Urm... scratch that last comment, you had it covered... took about 12
minutes with the order by / limit combination and about 6 minutes with the
max() method below thanks heaps!
select
itmmaster.id,suppstock.stockavail,suppstock.lastupdated
from
itmmaster left join suppstock on itmmaster.id=suppstock.id
where
itmmaster.indent=1 and itmmaster.publish=0 and
suppstock.lastupdated =
(select max(s2.lastupdated) from suppstock s2 where s2.id =
suppstock.id)
having
suppstock.stockavail>0;
2189 rows in set (6 min 18.54 sec)
Well that's nearly 2200 products I shouldn't have deleted from our website
(and shall rectify), not bad considering I removed just under 15,000
(which took 12 hours to do! I'll have to turn off indexing till the end of
the update next time.)
--
---<GRiP>---
Grant Parnell - SLUG President & LPIC-1 certified engineer
EverythingLinux services - the consultant's backup & tech support.
Web: http://www.elx.com.au/support.php
We're also busybits.com.au and linuxhelp.com.au and everythinglinux.com.au.
Phone 02 8756 3522 to book service or discuss your needs
or email us at paidsupport at elx.com.au
ELX or its employees participate in the following:-
OSIA (Open Source Industry Australia) - http://www.osia.net.au
AUUG (Australian Unix Users Group) - http://www.auug.org.au
SLUG (Sydney Linux Users Group) - http://www.slug.org.au
LA (Linux Australia) - http://www.linux.org.au
--
SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/
Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html
