Select rows with Max(Column Value) for each unique combination of two other columns
In MySQL 5.x you can use a sub-query.
SELECT *
FROM your_table
WHERE (`Group`, Dataset, RunNumber) IN (
SELECT `Group`, Dataset, MAX(RunNumber) AS MaxRunNumber
FROM your_table
GROUP BY `Group`, Dataset
);
Test on db<>fiddle here
Alternatives
--
-- LEFT JOIN on bigger
--
SELECT t.*
FROM your_table t
LEFT JOIN your_table t2
ON t2.`Group` = t.`Group`
AND t2.Dataset = t.Dataset
AND t2.RunNumber > t.RunNumber
WHERE t2.RunNumber IS NULL
ORDER BY t.`Group`, t.Dataset;
--
-- where NOT EXISTS on bigger
--
SELECT *
FROM your_table t
WHERE NOT EXISTS (
SELECT 1
FROM your_table t2
WHERE t2.`Group` = t.`Group`
AND t2.Dataset = t.Dataset
AND t2.RunNumber > t.RunNumber
)
ORDER BY `Group`, Dataset;
--
-- Emulating DENSE_RANK = 1 with variables
-- Works also in 5.x
--
SELECT RunNumber, `Group`, Dataset, Total
FROM
(
SELECT
@rnk:=IF(@ds=Dataset AND @grp=`Group`, IF(@run=RunNumber, @rnk, @rnk+1), 1) AS Rnk
, @grp := `Group` as `Group`
, @ds := Dataset as Dataset
, @run := RunNumber as RunNumber
, Total
FROM your_table t
CROSS JOIN (SELECT @grp:=null, @ds:=null, @run:=null, @rnk := 0) var
ORDER BY `Group`, Dataset, RunNumber DESC
) q
WHERE Rnk = 1
ORDER BY `Group`, Dataset;
--
-- DENSE_RANK = 1
-- MySql 8 and beyond.
--
SELECT *
FROM
(
SELECT *
, DENSE_RANK() OVER (PARTITION BY `Group`, Dataset ORDER BY RunNumber DESC) AS rnk
FROM your_table
) q
WHERE rnk = 1
ORDER BY `Group`, Dataset;
Fetch the rows which have the Max value for a column for each distinct value of another column
This will retrieve all rows for which the my_date column value is equal to the maximum value of my_date for that userid. This may retrieve multiple rows for the userid where the maximum date is on multiple rows.
select userid,
my_date,
...
from
(
select userid,
my_date,
...
max(my_date) over (partition by userid) max_my_date
from users
)
where my_date = max_my_date
"Analytic functions rock"
Edit: With regard to the first comment ...
"using analytic queries and a self-join defeats the purpose of analytic queries"
There is no self-join in this code. There is instead a predicate placed on the result of the inline view that contains the analytic function -- a very different matter, and completely standard practice.
"The default window in Oracle is from the first row in the partition to the current one"
The windowing clause is only applicable in the presence of the order by clause. With no order by clause, no windowing clause is applied by default and none can be explicitly specified.
The code works.
Related Topics
How to Have a Default Parameter for a MySQL Stored Procedure
Database Table With Million of Rows
How to Handle Duplicates Created by Left Join
Repeat Each Value in SQL Table N Times With 1:N in Different Column
Sql Server How to Find the Customers Who Have Bought a Product from Each Store
Converting to Timestamp With Time Zone Failed on Athena
Deleting Rows from Parent and Child Tables
Postgresql How to Insert a Value With Double Quotes Inside Double Quotes
Grouping But With Keeping All Non-Null Values
I Want to Give Serial No in My Query According to Group
How to Exclude Specific Row from Query in MySQL
How to Find the Row Count for All Your Tables in Postgres
How to Retrieve Microseconds or Milliseconds from MySQL Current Time
Count All Records Per Day in a Specific Month
How to Select Only 1 Row from Oracle SQL
Select Every Employee That Has a Higher Salary Than the Average of His Department
Select Distinct Values from One Table and Join With Another Table
T-Sql Query to Get the String Between Two Special Characters