Results 1 to 14 of 14

Thread: How do I get multiple columns with MAX SQL function?

  1. #1

    Thread Starter
    Frenzied Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    1,104

    How do I get multiple columns with MAX SQL function?

    I went to W3Schools and learned the MAX function. They have a "Try it" feature and SELECT ProductID, MAX(Price) FROM Products works. In SSMS SELECT Max(Time) FROM Accounts works but SELECT ID, Max(Time) FROM Accounts results in the error "Column 'Accounts.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." Is this a difference between SQL and TSQL or am I doing something wrong? How do I add columns?


    I know I can do a "Select Top 1" and order by date descending but I want to learn how to get the MAX function to work.

  2. #2
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: How do I get multiple columns with MAX SQL function?

    You need to use a GROUP BY clause with this type statement as example we have a table that records the access time of users in a database table. The table is named AccessHistory and contains the following fields:

    AccessHistoryID BIGINT
    PersonID BIGINT (foreign Keyed to Person Table)
    AccessDateTime DateTime

    In Person table we have
    PersonID BIGINT
    FirstName VARCHAR(20)
    MiddleName Varchar(20)
    LastName Varchar(40)

    So to get the last time anyone signed in we do the following

    sql Code:
    1. Select
    2.    P.FistName + ' ' + ISNULL(P.MiddleName,'') + ' ' + P.LastName as FullName
    3.   , MAX(AH.AccessDateTime)
    4. FROM AccessHistory AH
    5. INNER JOIN Person P ON
    6.    P.PersonID = AH.PersonID
    7. GROUP BY P.FistName + ' ' + ISNULL(P.MiddleName,'') + ' ' + P.LastName
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  3. #3

    Thread Starter
    Frenzied Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    1,104

    Re: How do I get multiple columns with MAX SQL function?

    Thank you. I grouped by ID and it works. Why?

  4. #4

    Thread Starter
    Frenzied Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    1,104

    Re: How do I get multiple columns with MAX SQL function?

    Oops. It does not work. Now I have all the rows. What am I doing wrong?

    SELECT ID, Max(Time) FROM Accounts Group By ID

  5. #5
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: How do I get multiple columns with MAX SQL function?

    If you use MAX function with other columns you need to tell the query that you need to get the MAX for each of these things that is the GROUP BY
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  6. #6
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: How do I get multiple columns with MAX SQL function?

    ID would be unique for each row so you get the value for each row. Are there other columns in the table?
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  7. #7
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: How do I get multiple columns with MAX SQL function?

    OK I went to the w2schools page and using there database name I did this:

    sql Code:
    1. SELECT
    2.     c.CustomerName
    3.     ,MAX(OrderDate) As LastOrder
    4. FROM Orders O
    5. Inner Join Customers C on
    6.     C.CustomerID = O.CustomerID
    7.   Group BY C.CustomerName;

    THis gets the last order date for each customer that placed and order. Adding this: Order by 2 DESC;
    (remove the ; after CustomerID) orders by the last order date descending
    Last edited by GaryMazzone; Feb 10th, 2017 at 02:59 PM.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  8. #8
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: How do I get multiple columns with MAX SQL function?

    If that orginal was from the same sample set change ProductID to SupplierID and watch the difference
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  9. #9
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: How do I get multiple columns with MAX SQL function?

    Quote Originally Posted by cory_jackson View Post
    I went to W3Schools and learned the MAX function. They have a "Try it" feature and SELECT ProductID, MAX(Price) FROM Products works. In SSMS SELECT Max(Time) FROM Accounts works but SELECT ID, Max(Time) FROM Accounts results in the error "Column 'Accounts.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." Is this a difference between SQL and TSQL or am I doing something wrong? How do I add columns?


    I know I can do a "Select Top 1" and order by date descending but I want to learn how to get the MAX function to work.
    Quote Originally Posted by cory_jackson View Post
    Thank you. I grouped by ID and it works. Why?
    It's a difference in implementation ... the SQL Standard simply states that the implementing DBMS has to allow aggregation of data - Max, Min, Sum, etc... but it doesn't explicitly say HOW that is to be done... so you get two variations... one that SQL Server and Access, and Oracle follows, where fields that are not part of an aggregation MUST be specified in the GROUP BY clause ... the other being what MySQL does, which makes GROUP BY optional, and if left off, will use the non-aggregated fields in an implied GROUP BY ... my guess this is what teh W3Schools is doing... running the queries against a MySQL back end, where such construct is allowed...

    Personally I have mixed feelings about it... but I came from a more explicit SQL structure (even before T-SQL and SQL Server) so using GroupBy is like breathing, I just do it and don't think twice about it.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10

    Thread Starter
    Frenzied Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    1,104

    Re: How do I get multiple columns with MAX SQL function?

    TechGnome it makes sense what you say about implementation. Thanks.

    Gary I don't have 2 tables or a need to join anything. How do I do this in TSQL with one table? I added a "Group by" to another column but I still get the error message when selecting two columns. While selecting the one column I get 3 seemingly random results. The field is Boolean so I think it's because in the table there are true, false, and null values. I added a where for the true condition and then it's one result but still if I add another column I get the error.

  11. #11

    Thread Starter
    Frenzied Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    1,104

    Re: How do I get multiple columns with MAX SQL function?

    Three times when I was trying to post my original message the forum software had a problem and dumped my text. In the final version I forgot to include the link to the W3Schools page I referenced. http://www.w3schools.com/sql/sql_func_max.asp

  12. #12

    Thread Starter
    Frenzied Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    1,104

    Re: How do I get multiple columns with MAX SQL function?

    I Believe I misunderstood the intended function of MAX. I believe now it's intended to return a scalar value. If I have that wrong please let me know.

  13. #13
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: How do I get multiple columns with MAX SQL function?

    of course it's a scalar function... what else would it do? It returns the MAXimum value of that field based on the grouping....
    so if I have this:
    Code:
    ID -- Color -- Count
    1      Blue      50
    2      Green    20
    3      White     10
    4     White      50
    5     Green      100
    And I doi this:
    select MAX(Count) from MyColorTable

    I'll get 100... that's the max value... since there was no grouping, it's the max for all records....
    If I do this:
    select Color, Max(Count) from MyColorTable Group By Color
    I should get this:
    Code:
    Color -- Count
    Blue      50
    White      50
    Green      100
    For each grouping of color, it returns the max value... for Blue, that's 50, for Green, 100, for White, 50


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  14. #14

    Thread Starter
    Frenzied Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    1,104

    Re: How do I get multiple columns with MAX SQL function?

    Oh, I see. That's a really awesome example you gave me. It makes it very clear now. Thank you.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width