1

Needs help in sql:

I need to group max online of each day by days (http://prntscr.com/a7j2sm)

my sql select:

SELECT id, date, MAX(online) 
FROM `record_online_1` 
GROUP BY DAY(date)

and result - http://prntscr.com/a7j3sp

This is incorrect result because, max online is correct, but date and id of this top online is incorrect. I dont have ideas how solve this issue..

UPD: using MySQL MariaDB

DarkNation
  • 11
  • 2
  • are you sure that is the right way to use group by, it's just that I have never used it that way. – Jay T. Feb 24 '16 at 22:18
  • 1
    What RDBMS are you using? I am assuming one that doesn't force you to include everything in your SELECT statement in either the GROUP BY or an aggregate function. – Langosta Feb 24 '16 at 22:19
  • Try to include `id` in the `group by` clause too. – Harsh Feb 24 '16 at 22:22
  • http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557 –  Feb 24 '16 at 22:44

1 Answers1

2

When you perform an aggregate functions, you have to include items in the SELECT statement that aren't a part of an aggregate function in the GROUP BY clause. In T-SQL, you simply cannot execute the above query if you don't also GROUP BY "id" for example. However, some database systems allow you to forego this rule, but it's not smart enough to know which ID it should bring back to you. You should only be doing this if, for example, all "ids" for that segment are the same.

So what should you do? Do this in two steps. Step one, find the max values. You will lose the ID and DATETIME data.

SELECT DAY(date) AS Date, MAX(online) AS MaxOnline
FROM `record_online_1` GROUP BY DAY(date)

The above will get you a list of dates with the max for each day. INNER JOIN this to the original "record_online_1" table, joining specifically on the date and max value. You can use a CTE, temp table, subquery, etc to do this.

EDIT: I found an answer that is more eloquent than my own.

Community
  • 1
  • 1
Langosta
  • 487
  • 3
  • 16