Help with One-to-Many query

Given the following two tables:
Parent Child
-- --
ParentID ParentID
ChildID
how can I retrieve the Child row which has the highest value of
ChildID within any single Parent row?
The following does not work.
SELECT * FROM Parent p
INNER JOIN Child c ON p.ParentID = c.ParentID
WHERE c.ChildID = (SELECT MAX(c.ChildID))
Thanks LarsI think you want:
SELECT ParentId, MAX(ChildId) AS ChildId
FROM Child
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"larzeb" <larzeb@.community.nospam> wrote in message
news:84f921dbjt4jumqo8j9mdt98oolp9knlqj@.
4ax.com...
> Given the following two tables:
> Parent Child
> -- --
> ParentID ParentID
> ChildID
> how can I retrieve the Child row which has the highest value of
> ChildID within any single Parent row?
> The following does not work.
> SELECT * FROM Parent p
> INNER JOIN Child c ON p.ParentID = c.ParentID
> WHERE c.ChildID = (SELECT MAX(c.ChildID))
> Thanks Lars|||Hit send too fast...
SELECT ParentId, MAX(ChildId) AS ChildId
FROM Child
GROUP BY ParentId
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"larzeb" <larzeb@.community.nospam> wrote in message
news:84f921dbjt4jumqo8j9mdt98oolp9knlqj@.
4ax.com...
> Given the following two tables:
> Parent Child
> -- --
> ParentID ParentID
> ChildID
> how can I retrieve the Child row which has the highest value of
> ChildID within any single Parent row?
> The following does not work.
> SELECT * FROM Parent p
> INNER JOIN Child c ON p.ParentID = c.ParentID
> WHERE c.ChildID = (SELECT MAX(c.ChildID))
> Thanks Lars|||SELECT *
FROM Parent p
INNER JOIN (select child.parentId, max(child.childId) as
childId, <other columns>
from child
group by child.parentId) as c
ON p.ParentID = c.ParentID
and p.childId = c.ChildId
This will work fine if you actually want all rows from parent matched with a
child. We might have to optimize some if you only want a small percentage
of the rows in parent.
--
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"larzeb" <larzeb@.community.nospam> wrote in message
news:84f921dbjt4jumqo8j9mdt98oolp9knlqj@.
4ax.com...
> Given the following two tables:
> Parent Child
> -- --
> ParentID ParentID
> ChildID
> how can I retrieve the Child row which has the highest value of
> ChildID within any single Parent row?
> The following does not work.
> SELECT * FROM Parent p
> INNER JOIN Child c ON p.ParentID = c.ParentID
> WHERE c.ChildID = (SELECT MAX(c.ChildID))
> Thanks Lars|||Try,
use northwind
go
select
oh.orderid,
oh.orderdate,
od.max_productid
from
orders as oh
inner join
(
select
orderid,
max(productid) as max_productid
from
[order details]
group by
orderid
) as od
on oh.orderid = od.orderid
-- or
select
oh.orderid,
oh.orderdate,
od.productid
from
orders as oh
inner join
[order details] as od
on oh.orderid = od.orderid
where
od.productid = (select max(a.productid) from [order details] as a where
a.orderid = oh.orderid)
AMB
"larzeb" wrote:

> Given the following two tables:
> Parent Child
> -- --
> ParentID ParentID
> ChildID
> how can I retrieve the Child row which has the highest value of
> ChildID within any single Parent row?
> The following does not work.
> SELECT * FROM Parent p
> INNER JOIN Child c ON p.ParentID = c.ParentID
> WHERE c.ChildID = (SELECT MAX(c.ChildID))
> Thanks Lars
>|||serveral ways to do it I guess, but you can use a sub-query for instance
SELECT
c.*
FROM
child c
INNER JOIN
(SELECT
parentid
,MAX(childID) ChildID
FROM
parent
GROUP BY
parentid) vt
ON c.childid = vt.childid
"larzeb" <larzeb@.community.nospam> wrote in message
news:84f921dbjt4jumqo8j9mdt98oolp9knlqj@.
4ax.com...
> Given the following two tables:
> Parent Child
> -- --
> ParentID ParentID
> ChildID
> how can I retrieve the Child row which has the highest value of
> ChildID within any single Parent row?
> The following does not work.
> SELECT * FROM Parent p
> INNER JOIN Child c ON p.ParentID = c.ParentID
> WHERE c.ChildID = (SELECT MAX(c.ChildID))
> Thanks Lars