Return entire row for Distinct COL1 which has Max(Col3) from Table1 AND get Avg(Col2) from Table2 where Col1 = Name1

My DB contains the following:

Table 1
COL1        COL2        COL3
Name1     Num1        Num1
Name2     Num2        Num2
Name3     Num3        Num3
Name2     Num4        Num4

COL1        COL2
Name1      Num1
Name2      Num2
Name3      Num3
Name2      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 It is highly likely I have no clue what I'm talking about.

Thanks to anyone that can help me out.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
Yes, it should only return one row.

I take it you mean you want to do the processing above for each and every distinct value of col1 in table 1.  Right?  That is, name1, name2 and name3 should all be listed with appropiate values.

If so, try something like this:

SELECT t1.col1, t1.col2, t1.col3, AVG(t2.col1)
      SELECT col1, MAX(col3) AS col3
      FROM table1
      GROUP BY col1
) AS t1
INNER JOIN table2 t2 ON t1.col1 = t2.col1
GROUP BY t1.col1, t1.col2, t1.col3
ORDER BY t1.col1

Scott PletcherSenior DBACommented:

D'OH, more like this:

SELECT t1.col1, t1.col2, t1.col3, AVG(CAST(t2.col2 AS DECIMAL(9, 2))) AS [col2 avg]
      SELECT col1, MAX(col3) AS col3
      FROM table1
      GROUP BY col1
) AS t1Max
INNER JOIN table1 t1 ON t1.col1 = t1Max.col1 AND t1.col3 = t1Max.col3
INNER JOIN table2 t2 ON t1.col1 = t2.col1
GROUP BY t1.col1, t1.col2, t1.col3
ORDER BY t1.col1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DallasWebCenterAuthor Commented:
Thanks Scott.
It will be a bit later in the evening before I can try this one out, but I look forward to it.
DallasWebCenterAuthor Commented:
Hi Scott-
Here is my actual SQL string. I'm currently getting an error that says 'Invalid Syntax near the word 'From''. Any thoughts? The .net error message does not give the character number of the error, although there are only two 'from' in the string.
I really not so good at this.

SQLString = "SELECT t1.Name, t1.Players, t1.Game, AVG(CAST(t2.Size AS DECIMAL(9, 2))) AS AvgSize FROM (SELECT Name, MAX(Game)) AS Game FROM GameTable GROUP BY Name AS t1Max INNER JOIN GameTable t1 ON t1.Name = t1Max.Name AND t1.Game = t1Max.Game INNER JOIN Game t2 ON t1.Name = t2.Name GROUP BY t1.Name, t1.Players, t1.Game ORDER BY t1.Name"

Any ideas?
DallasWebCenterAuthor Commented:
I changed just a bit of the string which Scott gave me. It works, so Scott, thanks for putting me in the right direction.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.