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.

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:

Select 
   P.FistName + ' ' + ISNULL(P.MiddleName,'') + ' ' + P.LastName as FullName
  , MAX(AH.AccessDateTime)
FROM AccessHistory AH
INNER JOIN Person P ON
   P.PersonID = AH.PersonID
GROUP BY P.FistName + ' ' + ISNULL(P.MiddleName,'') + ' ' + P.LastName

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

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

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

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

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

sql Code:

SELECT 
    c.CustomerName
    ,MAX(OrderDate) As LastOrder
FROM Orders O
Inner Join Customers C on 
    C.CustomerID = O.CustomerID
  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

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

Quote:

---

Originally Posted by cory_jackson

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

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

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.

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

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.

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

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