GROUP BY with MAX(DATE)
You cannot include non-aggregated columns in your result set which are not grouped. If a train has only one destination, then just add the destination column to your group by clause, otherwise you need to rethink your query.
Try:
SELECT t.Train, t.Dest, r.MaxTime
FROM (
SELECT Train, MAX(Time) as MaxTime
FROM TrainTable
GROUP BY Train
) r
INNER JOIN TrainTable t
ON t.Train = r.Train AND t.Time = r.MaxTime
Group by max date and id
Something like this should work:
SELECT t1.id, t1.value, t1.date
FROM your_table t1
INNER JOIN (
SELECT id, MAX(date) date
FROM your_table
GROUP BY id
) t2
ON t1.id = t2.id AND t1.date = t2.date
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).
Max value per group based on condition
I would do this as:
select filenumber,
coalesce(max(case when approved = 1 then version end),
max(version)
) as your_max_version
from t
group by filenumber;
In Oracle, you can also do this using keep
:
select filenumber,
max(version) keep (dense_rank first order by approved desc, version desc) as your_max_version
from t
group by filenumber;
I would speculate that the first version would be a little bit faster, but I am usually impressed by the performance of keep
.
SQL to get max value from each group
select * from [table] t1
inner join
(
select track_id, user_id, max(rating) maxRating
from [table]
group by track_id, user_id
) tmp
on t1.track_id = tmp.track_id
and t1.user_id = tmp.user_id
and t1.rating = tmp.maxRating;
SQL select max of a group
Have a sub-query that returns each fruit with its highest Production_line value. JOIN
with that result:
select f1.*
from fruits f1
join (select fruit, max(Production_line) as maxProduction_line
from fruits
group by fruit) f2
on f1.fruit = f2.fruit and f1.Production_line = f2.maxProduction_line
return rows of sql query with MAX(date) and GROUP BY id
SELECT t.id, t.position, r.Maxdate
FROM (
SELECT id, MAX(date) as Maxdate
FROM Yourtable
GROUP BY id
) r
INNER JOIN Yourtable t
ON t.id = r.id AND t.date = r.Maxdate
Related Topics
Is There a Performance Difference Between Cte , Sub-Query, Temporary Table or Table Variable
Find Overlapping Date Ranges in Postgresql
How to Return Rows with a Specific Value First
SQL (Oracle): Order by and Limit
Add Unique Constraint to Combination of Two Columns
SQL Statement to Get Column Type
Merge Overlapping Date Intervals
Selecting Random Rows in MySQL
Saving Changes After Table Edit in SQL Server Management Studio
How to Set Variable from a SQL Query
Protecting Against SQL Injection in Python
How to Find Current Transaction Level
How to Calculate a Running Total in SQL Without Using a Cursor
How to Delete from Select in MySQL
Mysql: What Is a Reverse Version of Like