Except I want ml.field, which is a data field, not a key. So I can't group by it.
Bascially the point of the subselect is to get the field value where serial is
hightest
and the two keys match.
Josh Berkus wrote:
> Joseph,
>
>
>>select d.field1, d.field2, u.field1, u.field2, (select ml.field from
>>mltable where ml.key1
>>= u.key1 and ml.key2 = u.key2 order by ml.serial desc limit 1) from
>>utable u, dtable d,
>>where u.key1 = d.key order by d.somefield limit 25 offset ???;
>>
>
>>From the look of it, you want to simply take the utable references out
> of the subselect. Try:
>
> SELECT d.field1, d.field2, u.field1, u.field2, mll.latest
> FROM utable u, dtable d,
> (SELECT key1, key2, max(serial) as latest
> FROM mltable
> GROUP BY key1, key2) mll
> WHERE u.key1 = d.key
> AND u.key1 = mll.key1 and u.key2 = mll.key2
> ORDER BY d.somefield
> LIMIT 25 OFFSET $pageno
>
> That should give you the utable and dtable records, plus the last serial
> value while executing the subselect only once per query call. Give it a
> spin.
>
> -Josh
>
>
> ______AGLIO DATABASE SOLUTIONS___________________________
> Josh Berkus
> Complete information technology [EMAIL PROTECTED]
> and data management solutions (415) 565-7293
> for law firms, small businesses fax 621-2533
> and non-profit organizations. San Francisco
>
--
Joseph Shraibman
[EMAIL PROTECTED]
Increase signal to noise ratio. http://www.targabot.com
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
