Sql:Using Group by and Max on Multiple Columns

SQL : Using GROUP BY and MAX on multiple columns

You can get the best attrib1 values, and then join in the attrib2 values and get the best of those for each attrib1 value:

select t2.catID, t2.attrib1, max(t2.attrib2)
from
(
  select catID, max(attrib1) as attrib1
  from test_table
  group by catID
) t1
inner join test_table t2 on t2.catID = t1.catID and t2.attrib1 = t1.attrib1
group by t2.catID, t2.attrib1

Using group by on multiple columns

Group By X means put all those with the same value for X in the one group.

Group By X, Y means put all those with the same values for both X and Y in the one group.

To illustrate using an example, let's say we have the following table, to do with who is attending what subject at a university:

Table: Subject_Selection

+---------+----------+----------+
| Subject | Semester | Attendee |
+---------+----------+----------+
| ITB001  |        1 | John     |
| ITB001  |        1 | Bob      |
| ITB001  |        1 | Mickey   |
| ITB001  |        2 | Jenny    |
| ITB001  |        2 | James    |
| MKB114  |        1 | John     |
| MKB114  |        1 | Erica    |
+---------+----------+----------+

When you use a group by on the subject column only; say:

select Subject, Count(*)
from Subject_Selection
group by Subject

You will get something like:

+---------+-------+
| Subject | Count |
+---------+-------+
| ITB001  |     5 |
| MKB114  |     2 |
+---------+-------+

...because there are 5 entries for ITB001, and 2 for MKB114

If we were to group by two columns:

select Subject, Semester, Count(*)
from Subject_Selection
group by Subject, Semester

we would get this:

+---------+----------+-------+
| Subject | Semester | Count |
+---------+----------+-------+
| ITB001  |        1 |     3 |
| ITB001  |        2 |     2 |
| MKB114  |        1 |     2 |
+---------+----------+-------+

This is because, when we group by two columns, it is saying "Group them so that all of those with the same Subject and Semester are in the same group, and then calculate all the aggregate functions (Count, Sum, Average, etc.) for each of those groups". In this example, this is demonstrated by the fact that, when we count them, there are three people doing ITB001 in semester 1, and two doing it in semester 2. Both of the people doing MKB114 are in semester 1, so there is no row for semester 2 (no data fits into the group "MKB114, Semester 2")

Hopefully that makes sense.

SQL Server group by and max value for multiple column

Here is a method that calculates the maximum for each column and then uses that information to find the overall row:

select t.*
from (select t.*,
             max(amnt1) over (partition by unit) as max1,
             max(amnt2) over (partition by unit) as max2
      from t
     ) t
where (t.amnt1 = max1 and max1 >= max2) or
      (t.amnt2 = max2 and max2 >= max1);

The way this works is by computing the maximum for each of the two columns for each unit. These maxima are in max1 and max2.

The where clause then says: Keep this row if amnt1 is the same as max1 and max1 is really the maximum. Similarly, keep the row if amnt2 is the same as max2 and max2is really the maximum.

The solution given by @ZLK is more general. If you had more columns then the logic gets more complicated to explain this way (eh, not really that hard, but apply would then look simpler). But for two columns, the apply nested before the row_number() starts to look a bit complicated.

How to Select max value or last record when grouping multiple columns?

Get the max date per id,country and join this result with the original table.

SELECT t.* 
from table t
join (select id,country,max(date) as maxdate from table 
      group by id,country) t1
on t1.id=t.id and t1.country=t.country and t1.maxdate=t.date

Mysql group by two columns and pick the maximum value of third column

Your query is fine. The reason you are getting 2000 rows is because you are getting one row for every unique pair of values user_id, item_id.

If you want to see the interaction types going into each row then use:

select user_id, item_id, max(interaction_type) as max_type,
       group_concat(distinct interaction_type) as interaction_types,
       count(*) as cnt
from mytable
group by user_id, item_id;

It occurs to me that you want all rows with the maximum interaction type. If so, calculate the maximum and then find all rows that match that value:

select t.*
from mytable t cross join
     (select max(interaction_type) as maxit from mytable) x
     on x.maxit = t.interaction_type;

No group by is needed for this query.

select max count after group by multiple columns

This one should work:

select t2.*
from (
select season, max(totalwins) as totalwins from (
select season, winner, count(*) as totalwins
  from matches t
  group by season, winner
) s1 group by season) w join
(select season, winner, count(*) as totalwins
  from matches t
  group by season, winner
) t2 on t2.season = w.season and t2.totalwins = w.totalwins;

Result:

season  winner  totalwins
------  ------- ---------
2014    Chelsea 2
2015    Arsenal 1
2015    Chelsea 1

Group by two columns, take sum, then max

just add row_number() partition by Name to your query and get the 1st row (order by total_score descending)

select  *
from
(
    -- your existing `total_score` query
    SELECT  Id, Name,
            SUM(Score) AS total_score,
            r = row_number() over (partition by Name order by SUM(Score) desc)
    FROM  Mytable
    GROUP BY Id, Name
) d
where   r = 1

SQL MAX of multiple columns?

This is an old answer and broken in many way.

See https://stackoverflow.com/a/6871572/194653 which has way more upvotes and works with sql server 2008+ and handles nulls, etc.

Original but problematic answer:

Well, you can use the CASE statement:

SELECT
    CASE
        WHEN Date1 >= Date2 AND Date1 >= Date3 THEN Date1
        WHEN Date2 >= Date1 AND Date2 >= Date3 THEN Date2
        WHEN Date3 >= Date1 AND Date3 >= Date2 THEN Date3
        ELSE                                        Date1
    END AS MostRecentDate

---

Related Topics