Michael
The subquery version of that logic
SELECT * FROM a
JOIN b ON a.id = b.a_id
WHERE b.id = (
SELECT MAX(c.id)
FROM b as c
WHERE c.a_id = a.id
);
likely runs slower. If the sorting column is also the joining column,
you can still write it as a join:
SELECT a.*, b.*
FROM a INNER JOIN b ON a.id=b.id
LEFT JOIN b AS c ON a.id=c.id and b.id < c.id
WHERE c.id IS NULL;
PB
-----
if the sorting column (id) is also the joining column (id).
PB
Michael Caplan wrote:
> Peter,
>
> Thanks for the reply. I was not able to get your query working as
> illustrated. I also realize that my example query was flawed, as I
> made no reference to an ordering column (as you point out). What I
> was able to get working is the following:
>
>
> SELECT
> *
> FROM
> a
> JOIN
> b
> ON
> a.id = b.a_id
> WHERE
> b.id =
> (
> SELECT
> MAX(C.ID)
> FROM
> B as C
> WHERE
> C.a_id = a.id
> )
>
>
> I'm wondering if you have any thoughts on this approach.
>
> Best,
>
> Michael
>
>
> Peter Brawley wrote:
>> Michael,
>>
>> >If I wanted all records from "a" and only the first record from "b",
>> >how would I integrate a LIMIT statement in this, or some other
>> >statement that would achieve the same end? Appending LIMIT
>> >to the end of the query will limit the entire result set, which is not
>> >the desired effect.
>>
>> Assuming "first" means the smallest joined value on orderingcol ...
>>
>> select a.*,b.*
>> from a inner join b on a.id=b.id
>> left join c on b.id=c.id and b.orderingcol < c.orderingcol
>> where c.orderingcol is null;
>>
>> PB
>>
>> -----
>>
>> Michael Caplan wrote:
>>> Hi there,
>>>
>>> I'm following up on a thread I started yesterday with a new thread,
>>> cause I'm now looking at a different problem: limiting the result
>>> of a join. For example:
>>>
>>> SELECT
>>> *
>>> FROM
>>> a
>>> JOIN
>>> b
>>> ON
>>> a.id = b.id
>>>
>>> If I wanted all records from "a" and only the first record from "b",
>>> how would I integrate a LIMIT statement in this, or some other
>>> statement that would achieve the same end? Appending LIMIT to the
>>> end of the query will limit the entire result set, which is not the
>>> desired effect.
>>>
>>> Any ideas?
>>>
>>> Thanks,
>>>
>>> Michael
>>>
>>
>>
>
>
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.10.7/409 - Release Date: 8/4/2006
