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
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 It is highly likely I have no clue what I'm talking about.

Thanks to anyone that can help me out.

Question by:DallasWebCenter
    LVL 68

    Expert Comment

    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)
    FROM (
          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 68

    Accepted Solution


    D'OH, more like this:

    SELECT t1.col1, t1.col2, t1.col3, AVG(CAST(t2.col2 AS DECIMAL(9, 2))) AS [col2 avg]
    FROM (
          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

    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

    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

    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

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    733 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

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now