Novice seeking help, Max Aggregate not returning expected results

how to use aggregate function in join query in sql server
sql join sum two tables
sql group by
sql having
join two tables and sum column
sql aggregate functions
how to combine two aggregate functions in sql
left join with aggregate function

I'm still very new to MS-SQL. I have a simple table and query that that is getting the best of me. I know it will something fundamental I'm overlooking.

I've changed the field names but the idea is the same. So the idea is that every time someone signs up they get a RegID, Name, and Team. The names are unique, so for below yes John changed teams. And that's my trouble.

            Football Table
+------------+----------+---------+
| Max_RegID  |   Name   |   Team  |
+------------+----------+---------+
|   100      |   John   |   Red   |
|   101      |   Bill   |   Blue  |
|   102      |   Tom    |   Green |
|   103      |   John   |   Green |
+------------+----------+---------+

With the query at the bottom using the Max_RegID, I was expecting to get back only one record.

+------------+----------+---------+
| Max_RegID  |   Name   |   Team  |
+------------+----------+---------+
|   103      |   John   |   Green |
+------------+----------+---------+

Instead I get back below, Which seems to include Max_RegID but also for each team. What am I doing wrong?

+------------+----------+---------+
| Max_RegID  |   Name   |   Team  |
+------------+----------+---------+
|   100      |   John   |   Red   |
|   103      |   John   |   Green |
+------------+----------+---------+

My Query

SELECT
  Max(Football.RegID) AS Max_RegID,
  Football.Name,
  Football.Team
FROM
  Football    
GROUP BY
  Football.RegID,
  Football.Name,
  Football.Team

EDIT* Removed the WHERE statement

The reason you're getting the results that you are is because of the way you have your GROUP BY clause structured.

When you're using any aggregate function, MAX(X), SUM(X), COUNT(X), or what have you, you're telling the SQL engine that you want the aggregate value of column X for each unique combination of the columns listed in the GROUP BY clause.

In your query as written, you're grouping by all three of the columns in the table, telling the SQL engine that each tuple is unique. Therefore the query is returning ALL of the values, and you aren't actually getting the MAX of anything at all.

What you actually want in your results is the maximum RegID for each distinct value in the Name column and also the Team that goes along with that (RegID,Name) combination.

To accomplish that you need to find the MAX(ID) for each Name in an initial data set, and then use that list of RegIDs to add the values for Name and Team in a secondary data set.

Caveat (per comments from @HABO): This is premised on the assumption that RegID is a unique number (an IDENTITY column, value from a SEQUENCE, or something of that sort). If there are duplicate values, this will fail.

The most straight forward way to accomplish that is with a sub-query. The sub-query below gets your unique RegIDs, then joins to the original table to add the other values.

SELECT
  f.RegID
 ,f.Name
 ,f.Team
FROM
  Football AS f
  JOIN
    (--The sub-query, sq, gets the list of IDs
      SELECT
        MAX(f2.RegID) AS Max_RegID
      FROM
        Football AS f2
      GROUP BY
        f2.Name
    ) AS sq
      ON
      sq.Max_RegID = f.RegID;

EDIT: Sorry. I just re-read the question. To get just the single record for the MAX(RegID), just take the GROUP BY out of the sub-query, and you'll just get the current maximum value, which you can use to find the values in the rest of the columns.

SELECT
  f.RegID
 ,f.Name
 ,f.Team
FROM
  Football AS f
  JOIN
    (--The sub-query, sq, now gets the MAX ID
      SELECT
        MAX(f2.RegID) AS Max_RegID
      FROM
        Football AS f2
    ) AS sq
      ON
      sq.Max_RegID = f.RegID;

An Introduction to Using SQL Aggregate , MAX, Returns the largest value in a column, SELECT MAX(column) FROM (For more info, see A Beginner's Guide to SQL Aggregate Functions.) Notice how the sum and calculated average results in a NULL value for Coyote Springs. This is not what I expected; I wanted to get ALL cities and a count of  1 I need help identifying group of table members that have different status in another table Sep 26 '18 0 Novice seeking help, Max Aggregate not returning expected results Sep 10 '18 Badges (4)

Use row_number()

    select * from 
(SELECT
      Football.RegID AS Max_RegID,
      Football.Name,
      Football.Team, row_number() over(partition by name order by Football.RegID desc) as rn
    FROM
      Football
    WHERE
      Football.Name = 'John')a
where rn=1

A Beginner's Guide to SQL Aggregate Functions, For example, let's get the minimum age from our group of users: In a similar yet opposite fashion to MIN , MAX returns the largest value in a column. Let's get If a record didn't have an age value, it would have been NULL (and not included in the COUNT result). This returns the expected result of “9”. QUERY will return all of the results that match the "where" clauses so I used the "order by" to sort by the quantity column and then the "limit 1" part to have it only return the first row of results. This is easier than using MAX(A) since you can't use MAX(A) in the where part. :( I wish we could.

simply you can edit your query below way

SELECT *      
FROM
  Football f
WHERE
  f.Name = 'John' and 
 Max_RegID = (SELECT  Max(Football.Max_RegID) where Football.Name = 'John'
         )

or if sql server simply use this

select top 1 * from Football f
where f.Name = 'John'
order by Max_RegID desc

or if mysql then

select  * from Football f
    where f.Name = 'John'
    order by Max_RegID desc
Limit 1

Overview of Aggregate Functions, MAX — returns the maximum value used within a specified column. However, an aggregate function DISTINCT BY(field2) field1 does not ignore the distinct The values listed within a given aggregate result value cannot be explicitly ordered. Instead, you get a person count for each state, the same as if the DISTINCT  It'd help if I knew the version number you are using and all the commands you used to get here. If you can write all of them either in sense or curl syntax that'd be helpful to me. ignaqui July 15, 2016, 9:33pm #4

You need self join :

select f1.*
from Football f inner join
     Football f1 
     on f1.name = f.name
where f.Max_RegID = 103;

After re-visit question, the sample data suggests me subquery :

select f.*
from Football f
where name = (select top (1) f1.name
              from Football f1
              order by f1.Max_RegID desc
             );

A Beginner's Guide to the True Order of SQL Operations – Java , SELECT first_name, MAX (last_name), count (*) The lexical (syntactical) order of SQL operations (clauses) does not correspond at all to the logical order of operations (although, In Java, you would get something like: Map<String, List<​Row>> . the aggregation step, so aggregate function results are. Re: Index match return min and max date MAX (as mentioned in other posts) can be MIN result + 6 so no need for MAX formula. And why not use the non-array formulae others provided.

Aggregate Functions in Tableau, Aggregations and floating-point arithmetic: The results of some aggregations may not always be exactly as expected. For example, you may find that the Sum  SQL MAX() on date value: Here we have discussed the usage of SQL MAX() function on date type of column of a table. SQL WHERE clause along with the SQL MAX() can be used as a subquery to find the maximum value of a column based upon some condition.

SQLite Subquery: An Ultimate Guide for SQLite The Novices, Typically, a subquery returns a single row as an atomic value, though it may return multiple Sometimes you want to apply aggregate functions to a column multiple times. Then, SQLite uses this result set as a derived table in the outer query. The correlated subquery is not efficient because it is evaluated for each row  This object contains the results returned by a query() if the query contains an aggregate function, such as MAX(). AggregateResult is an sObject, but unlike other sObject objects such as Contact, it is read-only and it is only used for query results. The QueryResult object has a records field that is an array of sObject records matching your query.

PromQL tutorial for beginners and humans, For instance, the query above may return time series with the following labels for eth0 , eth1 If there are no matches, then the time series is dropped from the result. When drawing graphs for gauges it is expected to see min, max, avg and/or quantile Help · Legal A button that says 'Get it on, Google Play', and if clicked. Stepwise not returning expected results in R. Ask Question Asked 2 years, 3 months ago. Asking for help, clarification, or responding to other answers.

Comments
  • Where regid = max .... Or select top 1 order by regid desc
  • The names are unique .. but you have 2 Jhons ...
  • Thanks to everyone, In my effort to make my question easier, I ended up asking the wrong question. What would I need to do if I didn't have the WHERE statement.
  • In explanation of your query: If you group by every column then you will get back every row (assuming that Max_RegId is unique, e.g. an identity column). Generally you group by one or more columns that have repeating values, e.g. Name, in order to summarize the data for each value. Tip: If you include the table schema then we don't have to guess at data types, identity columns, ... .
  • So now you want the maxId for each name instead of only Jhon?
  • Thanks to everyone who contributed. I always feel like like i'm trying to light a cigarette with a rocket engine when I ask questions here, but you guys always know how to respond that makes it easy to understand.
  • Great explanation with the caveat that it is based on the assumption that RegId is unique, e.g. an identity column. While likely, the OP has chosen to maintain plausible deniability about that. Still worthy of an upvote.
  • @HABO, thanks, and your caveat is worth noting, which I did in an edit just now.
  • After OP comment, he want this but without where
  • In your first solution there is no need to check for "John" in the outer query. The subquery has already filtered by Name.
  • @HABO lets put another id with different name what will be the max id in that case, i consider that also
  • If you added ( 104, 'Cindy', 'Paisley' ) then the subquery will filter it out since it doesn't match where Football.Name = 'John'. Checking again for "John" in the outer query still seems redundant if we assume that MaxRegId is unique.
  • is there any necessary of inner join after giving where condition =103
  • if you filter with 103 then how it can be maximum, you just consider a static value here
  • do you really need sub-query in this way for this answer??