How to Get All the Fields of a Row Using the SQL Max Function

How to get all the fields of a row using the SQL MAX function?

This is the greatest-n-per-group problem that comes up frequently. My usual way of solving it is logically equivalent to the answer given by @Martin Smith, but does not use a subquery:

SELECT T1.Id, T1.name, T1.type, T1.price 
FROM Table T1
LEFT OUTER JOIN Table T2
  ON (T1.type = T2.type AND T1.price < T2.price)
WHERE T2.price IS NULL;

My solution and all others given on this thread so far have a chance of producing multiple rows per value of type, if more than one product shares the same type and both have an equal price that is the max. There are ways to resolve this and break the tie, but you need to tell us which product "wins" in case like that.

You need some other attribute that is guaranteed to be unique over all rows, at least for rows with the same type. For example, if the product with the greater Id value should win, you can resolve the tie this way:

SELECT T1.Id, T1.name, T1.type, T1.price 
FROM Table T1
LEFT OUTER JOIN Table T2
  ON (T1.type = T2.type AND (T1.price < T2.price
       OR T1.price = T2.price AND T1.Id < T2.Id))
WHERE T2.price IS NULL;

SQL select only rows with max value on a column

At first glance...

All you need is a GROUP BY clause with the MAX aggregate function:

SELECT id, MAX(rev)
FROM YourTable
GROUP BY id

It's never that simple, is it?

I just noticed you need the content column as well.

This is a very common question in SQL: find the whole data for the row with some max value in a column per some group identifier. I heard that a lot during my career. Actually, it was one the questions I answered in my current job's technical interview.

It is, actually, so common that Stack Overflow community has created a single tag just to deal with questions like that: greatest-n-per-group.

Basically, you have two approaches to solve that problem:

Joining with simple group-identifier, max-value-in-group Sub-query

In this approach, you first find the group-identifier, max-value-in-group (already solved above) in a sub-query. Then you join your table to the sub-query with equality on both group-identifier and max-value-in-group:

SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM YourTable
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev

Left Joining with self, tweaking join conditions and filters

In this approach, you left join the table with itself. Equality goes in the group-identifier. Then, 2 smart moves:

1. The second join condition is having left side value less than right value
2. When you do step 1, the row(s) that actually have the max value will have NULL in the right side (it's a LEFT JOIN, remember?). Then, we filter the joined result, showing only the rows where the right side is NULL.

So you end up with:

SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
    ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;

Conclusion

Both approaches bring the exact same result.

If you have two rows with max-value-in-group for group-identifier, both rows will be in the result in both approaches.

Both approaches are SQL ANSI compatible, thus, will work with your favorite RDBMS, regardless of its "flavor".

Both approaches are also performance friendly, however your mileage may vary (RDBMS, DB Structure, Indexes, etc.). So when you pick one approach over the other, benchmark. And make sure you pick the one which make most of sense to you.

How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL?

You are so close! All you need to do is select BOTH the home and its max date time, then join back to the topten table on BOTH fields:

SELECT tt.*
FROM topten tt
INNER JOIN
    (SELECT home, MAX(datetime) AS MaxDateTime
    FROM topten
    GROUP BY home) groupedtt 
ON tt.home = groupedtt.home 
AND tt.datetime = groupedtt.MaxDateTime

SQL: Get all columns on MAX

This will return one row with the highest kg.

SELECT top 1 *
FROM Test
WHERE exerVariName = 'Comp'
order by kg desc;

But since SQL doesn't guarantee order, if two rows have the same kg there is no guarantee you'll get the same row each time. To guarantee order you could also order by ID like this:

SELECT top 1 *
FROM Test
WHERE exerVariName = 'Comp'
order by kg desc, id;

You should replace * with only the columns you want to return.

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

SQL MAX of multiple columns and retrieve each row

This should be fastest and simplest:

(SELECT 'kills' AS what, kills, gamemode, id   FROM matches ORDER BY kills DESC, id LIMIT 1)
UNION ALL
(SELECT 'deaths'       , deaths, gamemode, id  FROM matches ORDER BY deaths DESC, id LIMIT 1)
UNION ALL
(SELECT 'assists'      , assists, gamemode, id FROM matches ORDER BY assists DESC, id LIMIT 1)
--  more ...

db<>fiddle here

Add id as second ORDER BY expression. This way, if multiple rows tie for the highest score, the row with the smallest id is chosen.

All parentheses are required. See:

• Combining 3 SELECT statements to output 1 table
• Create a unique index on a non-unique column

If any of the ORDER BY columns can be NULL, add NULLS LAST. See:

• Sort by column ASC, but NULL values first?

Select info from table where row has max date

SELECT group,MAX(date) as max_date
FROM table
WHERE checks>0
GROUP BY group

That works to get the max date..join it back to your data to get the other columns:

Select group,max_date,checks
from table t
inner join 
(SELECT group,MAX(date) as max_date
FROM table
WHERE checks>0
GROUP BY group)a
on a.group = t.group and a.max_date = date

Inner join functions as the filter to get the max record only.

FYI, your column names are horrid, don't use reserved words for columns (group, date, table).

Selecting all corresponding fields using MAX and GROUP BY

without a single primary key field, I think your best bet is:

select * from deal_status
inner join
  (select deal_id as did, max(timestamp) as ts
  from deal_status group by deal_id) as ds
  on deal_status.deal_id = ds.did and deal_status.timestamp = ds.ts

this still won't work if you allow having two different statuses for the same product at the same time

Get records with max value for each group of grouped SQL results

There's a super-simple way to do this in mysql:

select * 
from (select * from mytable order by `Group`, age desc, Person) x
group by `Group`

This works because in mysql you're allowed to not aggregate non-group-by columns, in which case mysql just returns the first row. The solution is to first order the data such that for each group the row you want is first, then group by the columns you want the value for.

You avoid complicated subqueries that try to find the max() etc, and also the problems of returning multiple rows when there are more than one with the same maximum value (as the other answers would do)

Note: This is a mysql-only solution. All other databases I know will throw an SQL syntax error with the message "non aggregated columns are not listed in the group by clause" or similar. Because this solution uses undocumented behavior, the more cautious may want to include a test to assert that it remains working should a future version of MySQL change this behavior.

Version 5.7 update:

Since version 5.7, the sql-mode setting includes ONLY_FULL_GROUP_BY by default, so to make this work you must not have this option (edit the option file for the server to remove this setting).

SQL get all names that have their MAX() where month='jan'

Here is one method:

select t.*
from t
where t.value = (select max(t2.value) from t t2 where t2.name = t.name) and
      t.month = 'jan';

You can also use window functions:

select t.*
from (select t.*,
             max(value) over (partition by name) as max_value
      from t
     ) t
where value = max_value and month = 'jan'

---

Related Topics