Return entire row for Distinct COL1 which has Max(Col3) from Table1 AND get Avg(Col2) from Table2 where Col1 = Name1
Posted on 2004-11-29
My DB contains the following:
COL1 COL2 COL3
Name1 Num1 Num1
Name2 Num2 Num2
Name3 Num3 Num3
Name2 Num4 Num4
Table 1 has about 1000 entries with many repeating names.
I want to return the entire row of data from Table1 with Distinct COL1 that has the Max(COL3).
Using my example above the query would return Name2, Num4, Num4
This is the SQL string I have right now which accomplishes this.
SQLString = "select t.Col1, t.Col2, t.Col3 from Table1 t where t.Col3 = (select max(Col3) from Table1 where Col1 = t.Col1) order by Col1"
It appears to work fine. And now here's my issue.
I want to return the following data:
Name2, Num4, Num4, and X (where X is the AVG of all Col2 in Table2 where Col1 = Name2)
Bloody hell. I hope that made sense.
I tried using this, but it only returned one row.
SQLString = "select COL1, Col2, Col3,(select avg(Col2) from Table2 where Table2.Col1=Table1.Col1) as AVGValue from Table1 where Col3 = (select max(Col3) from Table1 where Col1 = Table1.Col1) order by Col1"
I'm trying to put all of this into a datagrid in Vb.net. It is highly likely I have no clue what I'm talking about.
Thanks to anyone that can help me out.