Get max value per day with the corresponding name
Use distinct on
:
select distinct on (day) t.*
from t
order by day, val desc;
SQL select issue : get max value for each day
You already use GROUP BY
the distinct
is no make sense, so the distinct
can be removed.
then you just modify CONVERT(Date,attemptdate)
instead of attemptdate
in Group by
, and only need to group by CONVERT(Date,attemptdate)
select CONVERT(Date,attemptdate) as Date, max(currentcount) as MAXUSERS
from logintracking
where attemptdate between @dateDebut and @dateFin
and logintracking.clientaddr in ('10.118.254.21', '10.118.254.156')
group by CONVERT(Date,attemptdate)
Note:
Your @dateDebut
and @dateFin
already are Date
type. No need to use CONVERT
function.
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).
find single MAX value per day
You dont tell us what SQL product your using, so here is Sql Server 2008R2:
SELECT
CAST(t.DateColumn as DATE), MAX(t.PowerUsage) as 'Peak Power Each Day'
FROM [MyTable] t
GROUP BY CAST(t.DateColumn AS DATE)
here is MySql
SELECT
DATE(t.DateColumn), MAX(t.PowerUsage) as 'Peak Power Each Day'
FROM [MyTable] t
GROUP BY DATE(t.DateColumn)
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
Show only the max value of each group
If you just want the max amount, with respect to the date- you can take the MAX and cast the datetime as a date:
DECLARE @temp TABLE (groupno int, [user] varchar(20), status varchar(20), date datetime, amount int)
INSERT INTO @temp
VALUES
(74,'user1', 'status_1', '2016-01-01 05:40:00.0', 900)
,(74,'user1', 'status_1', '2016-01-01 05:45:00.0', 1200)
,(79,'user1', 'status_2', '2016-01-01 05:45:31.0', 31)
,(79,'user1', 'status_2', '2016-01-01 05:50:00.0', 300)
,(79,'user1', 'status_2', '2016-01-01 05:55:00.0', 600)
,(79,'user1', 'status_2', '2016-01-01 06:00:00.0', 900)
,(79,'user1', 'status_2', '2016-01-01 06:05:00.0', 1200)
,(90,'user1', 'status_1', '2016-01-01 06:07:52.0', 172)
,(90,'user1', 'status_1', '2016-01-01 06:10:00.0', 300)
,(90,'user1', 'status_1', '2016-01-01 06:15:00.0', 600)
SELECT groupno, [user], [status], CAST([date] as date) [Date], MAX(amount) as MAXamount
FROM @temp
GROUP BY groupno, [user], [status], CAST([date] as date)
Related Topics
How to Send a Query Result in CSV Format
How to Get Referenced Values from Another Table
How to Delete All Duplicate Records from SQL Table
Sqlite: Accumulator (Sum) Column in a Select Statement
Generate Series of Week Intervals for Given Month
Way to Abort Execution of MySQL Scripts (Raising Error Perhaps)
SQL Query for Courses Enrolment on Moodle
PHP Is Truncating Mssql Blob Data (4096B), Even After Setting Ini Values. am I Missing One
Freetds and Unixodbc Character Converting
Undo Log Error: No More Space Left Over in System Tablespace for Allocating Undo Log Pages
Is Innodb Sorting Really That Slow
Find a Database with a Particular Table or Find a Table in Every Database of SQL Server
Failing Update Table in Db2 with Sqlcode: -668, Sqlstate: 57016, Sqlerrmc: 7;
How to Execute SQL Queries in Apache Spark
Create a Sqlite View Where a Row Depends on The Previous Row