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
Medium Priority
Last Modified: 2009-07-29
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 Vb.net. It is highly likely I have no clue what I'm talking about.

Thanks to anyone that can help me out.

Question by:DallasWebCenter
  • 3
  • 2
LVL 70

Expert Comment

by:Scott Pletcher
ID: 12698253
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

LVL 70

Accepted Solution

Scott Pletcher earned 2000 total points
ID: 12698323

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

Author Comment

ID: 12700491
Thanks Scott.
It will be a bit later in the evening before I can try this one out, but I look forward to it.

Author Comment

ID: 12705664
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?

Author Comment

ID: 12709047
I changed just a bit of the string which Scott gave me. It works, so Scott, thanks for putting me in the right direction.

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question