Question

Is it possible to use a select statement so that it returns Max and min columns of a child row foreach Parent record?

So foreach parent record that has MANY child records I want to see the Max and Min of those child records for any given column.

How can I do this within a single select statement?

It should read something like: Return to me all parent records between created dates of such and such. Foreach parent record returned, show me the maximum value of column "a" in all of its child rows and also show me the minimum value of column "a" in all of its child rows.

Final Result Should show:

ParentID, MaxChildColumna, MinChildColumna

Answer 1

You can do something like this:

select p.id, max(c.a), min(c.a)
from parent as p
left outer join child as c
on c.parentid = p.id
group by p.id;

友情链接