Selecting All Corresponding Fields Using Max and Group By

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

MySQL - SELECT MAX(), and get corresponding fields

Could you try with the query below:

SELECT  T1.ID_employee,  
        T1.ID_department, 
        CASE WHEN maxs.max_end_date = "0000-00-00" THEN NULL ELSE maxs.max_end_date END AS end_date, 
        T1.rate
FROM TestTable T1
JOIN (  SELECT  id_employee,
                MAX(ID_department) AS ID_department,
                IF (MAX( end_date IS NULL ) = 1, "0000-00-00",  MAX( end_date )) AS max_end_date
        FROM TestTable 
        GROUP BY id_employee ) maxs ON maxs.id_employee = T1.id_employee AND maxs.ID_department = T1.ID_department
WHERE maxs.max_end_date = IFNULL(T1.end_date, "0000-00-00")
GROUP BY T1.id_employee

Please find the Live Demo

UPDATE:

As per the comments the following query helped to achieve the result:

SET @CurrentDate := CURDATE();

SELECT T2.ID_employee, 
       T2.ID_department, 
       CASE WHEN MR.Max_end_date = @CurrentDate THEN NULL ELSE T2.end_date END AS end_date, 
       MR.MaxRate AS rate
FROM TestTable T2 
JOIN (
    SELECT T1.ID_employee, MAX(T1.rate) AS MaxRate, MD.Max_end_date
    FROM TestTable T1
    JOIN (
        SELECT  ID_employee,
                MAX(CASE WHEN end_date IS NULL THEN @CurrentDate ELSE end_date END) AS Max_end_date
        FROM TestTable 
        GROUP BY ID_employee
        ) MD ON MD.ID_employee = T1.ID_employee
    WHERE MD.Max_end_date = IFNULL(T1.end_date, @CurrentDate)
    GROUP BY T1.ID_employee
) MR ON MR.ID_employee = T2.ID_employee AND MR.MaxRate = T2.rate 
WHERE MR.Max_end_date = IFNULL(T2.end_date, @CurrentDate)

Working Demo

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).

select max, group by and display other column that's not in group by clause

Try the following query:

Solution #1:

SELECT 
    products.name,
    products.type,
    products.price 
FROM products 
INNER JOIN 
( 
    SELECT type,MAX(price) max_price
    FROM products 
    GROUP BY type  ) t
ON products.type = t.type
AND products.price = t.max_price;

Demo Here

Solution #2:

SELECT
    products.name,
    products.type,
    products.price 
FROM
    products
WHERE   (type, price) IN (
        SELECT type, MAX(price) max_price
        FROM products
        GROUP BY type )

See Demo

EDIT:

---

Note: Both solutions might give you multiple products under same type if they share the same maximum price.

If you strictly want at most one item from each type then you need to group by again in the last line.

So for both solutions the last line would be:

GROUP BY products.type, products.price

See Demo of it

Get Max value and corresponding column

This query should get what you need:

SELECT
    player_id, game_id, score
FROM
(
    SELECT game_id,MAX(score) AS MaxScore
    FROM games
    GROUP BY game_id
) AS Winners
JOIN games
    ON (games.game_id = Winners.game_id AND games.score = Winners.MaxScore)

It assumes that a tie is a win for both players.

SQLFiddle

If you want to get just the player and their number of wins, you can use this query:

SELECT
    player_id, COUNT(*) AS wins
FROM
(
    SELECT game_id,MAX(score) AS MaxScore
    FROM games
    GROUP BY game_id
) AS Winners
JOIN games
    ON (games.game_id = Winners.game_id AND games.score = Winners.MaxScore)
WHERE player_id = {player_id}
GROUP BY player_id

Just replace {player_id} with the player you're looking for and wins is their number of wins or ties.

Select max() with corresponding field - No Groups

The easiest thing would be to simply order by the field and take the first result.

select Revision, Status from MY_TABLE_NAME where ID=1 order by Revision desc limit 1

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 Query to get column values that correspond with MAX value of another column?

I would try something like this:

SELECT
   s.video_id
   ,s.video_category
   ,s.video_url
   ,s.video_date
   ,s.video_title
   ,short_description
FROM videos s
   JOIN (SELECT MAX(video_id) AS id FROM videos GROUP BY video_category) max
      ON s.video_id = max.id

which is quite faster that your own solution

mySQL - Select by max date and get other fields of corresponding row

You need to start by grabbing the max date for each user like this:

SELECT user_id, MAX(date) AS latestDate
FROM myTable
GROUP BY user_id;

Once you have those values, you can join them back to your original table on the condition that the user_id and latestDate columns match so you can get the whole row. The final query is like this:

SELECT m.*
FROM myTable m
JOIN(
   SELECT user_id, MAX(date) AS latestDate
   FROM myTable
   GROUP BY user_id) tmp ON tmp.user_id = m.user_id AND tmp.latestDate = m.date;

Here is an SQL Fiddle example.

---

Related Topics