Single Max Child Row (Based on a Count) for All Parent Rows

**Infide** · Jul 11 '07, 02:23 AM

Originally posted by epiphyte

Let's say I have a table with a list of young folk (table1). This table is related to a table that lists all their booty calls (table2). A third table lists their booty calls' booty calls (table3). For the purposes of this example the tables are set up as a one to many relationship.

The young folks in table1 want to lower their risk of contracting an STD by kicking their single most sexually active booty call to the curb. So the challenge is to create a query that displays all the young folk in table1 along with who they should kick to the curb. Who they should kick to the curb is based on how many booty calls their booty calls have.

This is where I'm at...

select t1.ID, t2.ID,
(Select Count(t3.ID) from table3 t3 Where t3.Parent_ID = t2.ID) as BootyCalls
From table1 t1
join table2 t2 on t2.Parent_ID = t1.ID

That query displays all the rows from table1, all the rows from table2 and a BootyCall column which displays the count of table2's booty calls. The problem is that I want to display all the young folk in table1 and their single most sexually active booty call from table2.

Any ideas how to accomplish this in a single query? Thanks in advance for any help!

try

Code:

SELECT T1.*, T2.*, bootydata.*
FROM TABLE1
inner join table t2
	on t2.id = t1.id
inner join
    (SELECT Max(bootycalls), t3.id,t2.id
		FROM 
		(SELECT count(*) as bootycalls, t3.id, t2.id
                 from table3 t3 inner join 
                         table t2 on t2.id = t3.id
                 GROUP BY t3.id, t2.id) as BootyData
on t2.id = bootydata.id

The trick is the sub select which returns the max count of another sub select that performs the required aggregating.
The grouping of the derived table should provide a 1 to 1 match up of the people in table 2 with their maximum count of children in table 3.

**epiphyte** · Jul 11 '07, 02:21 PM

Originally posted by Infide

try

Code:

SELECT T1.*, T2.*, bootydata.*
FROM TABLE1
inner join table t2
	on t2.id = t1.id
inner join
    (SELECT Max(bootycalls), t3.id,t2.id
		FROM 
		(SELECT count(*) as bootycalls, t3.id, t2.id
                 from table3 t3 inner join 
                         table t2 on t2.id = t3.id
                 GROUP BY t3.id, t2.id) as BootyData
on t2.id = bootydata.id

Thanks! But it's missing a parentheses in this line "GROUP BY t3.id, t2.id) as BootyData" and trying to close it results in various errors such as multi-part identifiers unable to be bound and invalid columns.

**Infide** · Jul 11 '07, 03:50 PM

Code:

SELECT T1.*, T2.*, bootydata.*
FROM TABLE1
inner join table t2
    on t2.id = t1.id
inner join
    (SELECT Max(bootycalls), t3.id,t2.id
        FROM 
        (SELECT count(*) as bootycalls, t3.id, t2.id
                 from table3 t3 inner join 
                         table t2 on t2.id = t3.id
                 GROUP BY t3.id, t2.id) as BootyData
on t2.id = bootydata.id

Originally posted by epiphyte

Thanks! But it's missing a parentheses in this line "GROUP BY t3.id, t2.id) as BootyData" and trying to close it results in various errors such as multi-part identifiers unable to be bound and invalid columns.

Looks like I didn't alias my columns in my derived table and I missed a group by. My bad.

The trick is creating a derived table for your third table that summarizes what you want. You need to know table 2's most frequent booty calls from table 3?

This select:
(SELECT Max(bootycalls) , t3id,t2id
FROM
(SELECT count(*) as bootycalls, t3.id as t3id, t2.id as t2id
from table3 t3 inner join
table t2 on t2.id = t3.id
GROUP BY t3.id, t2.id) as BootyData
GROUP BY t3id,t2id
) as Data

This gives you the summary of highest t3 child counts to the t2 parent. Join this derived table back onto the t2 table and then t1 table.

Single Max Child Row (Based on a Count) for All Parent Rows

Single Max Child Row (Based on a Count) for All Parent Rows

Comment

Comment

Comment