Josh Berkus wrote:
> Joseph,
>
>
>>Actually I do it all the time, in the select part.
>>
>
> Hmm. Frankly, I didn't know that Subselects in the field list were
> supported, so this is a new one on me.
>
>
>
>>Well that is the problem. In my subselect I only want the latest
>>value of ml.field, which
>>I get my ordering my ml.keyfield. I don't see how group by could
>>help. I would need to
>>do something like: ... WHERE mll.keyfield = (select ml.keyfield from
>>mltable ml where ...
>>order by ml.keyfield desc limit 1) which would be a subselect again
>>which is what I'm
>>trying to avoid.
>>
>>SQL is really annoying.
>>
>
> If you think SQL is annoying, you should try relational calculus ;-)
>
> Think you can provide me a simplified version of your table structure
> and the results you're trying to get out of your query? I think that
> your problem is solvable with a little nested subselect, but I'm having
> a lot of difficulty picturing what we're looking at.
>
Basically there is the utable, which has two keys in it. I want to select some rows
from
the utable. Some columns come from the dtable, which has a key that matches to one of
the
utable keys, and I want to select the last value from the mltable which has the same
two
keys as utable and it has a serial value that I use do determine which is the last
entry.
I also want to use the last value from the mltable in the WHERE clause.
So the select is basically
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 ???;
The tricky part is the mltable. I don't want to put the whole subselect into the
where
clause because the mltable lookup is the most expensive part. And I need to have the
limit and offset be done by the entries in the utable/dtable.
--
Joseph Shraibman
[EMAIL PROTECTED]
Increase signal to noise ratio. http://www.targabot.com
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
