Group by functioning in sql

Question

I am running below queries on my table.

Table:

+----+-------------+--------------+----------------------------+------------+--------+
| id | Qty_holding | Qty_reserved | created                    | tokenid_id | uid_id |
+----+-------------+--------------+----------------------------+------------+--------+
|  1 |          10 |            0 | 2018-01-18 10:52:14.957027 |          1 |      1 |
|  2 |          20 |            0 | 2018-01-18 11:20:08.205006 |          8 |      1 |
|  3 |         110 |            0 | 2018-01-18 11:20:21.496318 |         14 |      1 |
|  4 |          10 |            0 | 2018-01-23 14:26:49.124607 |          1 |      2 |
|  5 |           3 |            0 | 2018-01-23 15:00:26.876623 |         11 |      2 |
|  6 |           7 |            0 | 2018-01-23 15:08:41.887240 |         11 |      2 |
|  7 |          11 |            0 | 2018-01-23 15:22:48.424224 |         11 |      2 |
|  8 |          15 |            0 | 2018-01-23 15:24:03.419907 |         11 |      2 |
|  9 |          19 |            0 | 2018-01-23 15:24:26.531141 |         11 |      2 |
| 10 |          23 |            0 | 2018-01-23 15:27:11.549538 |         11 |      2 |
| 11 |          27 |            0 | 2018-01-23 15:27:24.162944 |         11 |      2 |
| 12 |   7.7909428 |   0.11459088 | 2018-01-23 15:27:24.168643 |          1 |      2 |
| 13 |           3 |            0 | 2018-01-23 15:36:51.412340 |         14 |      2 |
| 14 |   7.5585988 |   0.11459088 | 2018-01-23 15:36:51.417177 |          1 |      2 |
| 15 |           6 |            0 | 2018-01-24 08:43:46.635069 |         14 |      2 |
| 16 |   7.3262548 |   0.11459088 | 2018-01-24 08:43:46.639984 |          1 |      2 |
| 17 |           9 |            0 | 2018-01-24 10:09:08.207816 |         14 |      2 |
| 18 |   7.0939108 |   0.11459088 | 2018-01-24 10:09:08.212842 |          1 |      2 |
| 19 |           6 |            3 | 2018-01-24 13:43:08.929586 |         14 |      2 |
| 20 |           3 |            6 | 2018-01-24 14:49:56.960112 |         14 |      2 |
| 21 |           0 |            9 | 2018-01-24 14:50:33.423671 |         14 |      2 |
| 22 |          30 |            9 | 2018-01-24 14:51:14.865453 |         14 |      2 |
| 23 |   4.7704708 |   0.11459088 | 2018-01-24 14:51:14.870256 |          1 |      2 |
| 24 |          27 |           12 | 2018-01-24 14:56:56.914009 |         14 |      2 |
| 25 |          24 |           15 | 2018-01-24 14:57:56.475939 |         14 |      2 |
| 26 |          21 |           15 | 2018-01-24 14:58:06.750903 |         14 |      2 |
| 27 |          18 |           15 | 2018-01-24 15:02:43.203878 |         14 |      2 |
| 28 |   4.7705074 |   0.11459088 | 2018-01-24 15:02:43.224901 |          1 |      2 |
| 29 |          24 |            0 | 2018-01-24 15:03:40.421943 |         11 |      2 |
| 30 |   4.9535074 |   0.11459088 | 2018-01-24 15:03:40.441552 |          1 |      2 |
| 31 |           1 |            0 | 2018-01-26 10:35:33.173801 |         18 |      2 |
| 32 |          10 |           15 | 2018-01-26 12:46:03.780807 |         14 |      2 |
+----+-------------+--------------+----------------------------+------------+--------+

Query 1:

select uid_id
     , tokenid_id
     , max(created) as max_created 
  from accounts_userholding 
 group 
    by uid_id
     , tokenid_id 

+--------+------------+----------------------------+
| uid_id | tokenid_id | max_created                |
+--------+------------+----------------------------+
|      1 |          1 | 2018-01-18 10:52:14.957027 |
|      1 |          8 | 2018-01-18 11:20:08.205006 |
|      1 |         14 | 2018-01-18 11:20:21.496318 |
|      2 |          1 | 2018-01-24 15:03:40.441552 |
|      2 |         11 | 2018-01-24 15:03:40.421943 |
|      2 |         14 | 2018-01-26 12:46:03.780807 |
|      2 |         18 | 2018-01-26 10:35:33.173801 |
+--------+------------+----------------------------+

Query 2:

select uid_id
     , Qty_holding
     , Qty_reserved tokenid_id
     , max(created) as max_created 
  from accounts_userholding 
 group 
    by uid_id
     , tokenid_id 

+--------+-------------+--------------+------------+----------------------------+
| uid_id | Qty_holding | Qty_reserved | tokenid_id | max_created                |
+--------+-------------+--------------+------------+----------------------------+
|      1 |          10 |            0 |          1 | 2018-01-18 10:52:14.957027 |
|      1 |          20 |            0 |          8 | 2018-01-18 11:20:08.205006 |
|      1 |         110 |            0 |         14 | 2018-01-18 11:20:21.496318 |
|      2 |          10 |            0 |          1 | 2018-01-24 15:03:40.441552 |
|      2 |           3 |            0 |         11 | 2018-01-24 15:03:40.421943 |
|      2 |           3 |            0 |         14 | 2018-01-26 12:46:03.780807 |
|      2 |           1 |            0 |         18 | 2018-01-26 10:35:33.173801 |
+--------+-------------+--------------+------------+----------------------------+

The Qty_holding value in above is not corresponding to latest date. For instance for tokenid_id 14 and uid_id as 2 latest record is

| 32 |          10 |           15 | 2018-01-26 12:46:03.780807 |         14 |      2 |

But above query is giving qty_holding as 3.

Any insights in functioning of mysql will be helpful . Thanks!

This is, incidentally, the most frequently asked question under this tag. — Strawberry, Jan 26 '18 at 13:32
SELECT a.* FROM table a JOIN (query1) b ON things in b equal things in a — Strawberry, Jan 26 '18 at 13:34
@CodeLღver: I am trying to understand the behavor of SQL in this case. Any help in this will help. — Manish Kumar, Jan 26 '18 at 13:37
@CodeLღver: select uid_id,Qty_holding,Qty_reserved, tokenid_id, max(created) as max_created from accounts_userholding group by uid_id, tokenid_id order by created desc; It also shows the same result — Manish Kumar, Jan 26 '18 at 13:42
Because the column isn't in the group, it picks an arbitary row from the group (the first one it finds). There is no definition provided as to which this will be and you should NEVER rely on which row's value will be returned. — Philip Couling, Jan 26 '18 at 14:02
For future reference, it's unclear what you're actually asking here. "Why is mysql doing this?" - needs to be more clearly stated. "How do I fix this?" - needs to include a description of what you're trying to achieve... and that Question. — Philip Couling, Jan 26 '18 at 14:07

score 1 · Accepted Answer · answered Jan 26 '18 at 14:01

1

As a rule of thumb: When you mix normal columns with aggregate functions in SELECT, you need to use GROUP BY. Do not use GROUP BY when you do not have normal columns and aggregate functions in SELECT.

The thing to put into the GROUP BY, is all from SELECT but the aggregate functions (and possible constants).

As an example if you have a query:

select a, substring(b,3), 'x', max(y)
from yourtable

You need to use GROUP BY. You leave out 'x' as it is a constant and you leave out the aggregate function. The rest goes to the GROUP BY.

select a, substring(b,3), 'x', max(y)
from yourtable
group by a, substring(b,3)

Previous MySQL versions allowed quite liberal use of GROUP BY resulting quite often just bad/incorrect code.

answered Jan 26 '18 at 14:01

slaakso

8,331
2
16
27

MySQL only changed the default value of the setting that determines if the relaxed group by mode can used. It still allows the relaxed mode if you change the settings and vica versa: the older versions could also be secured against such use. – Shadow Jan 26 '18 at 14:56
Yes, that is true. However, most of the users do not change the default settings (unless they have to) making the ONLY_FULL_GROUP_BY sql_mode value something users start obeying. And that is a good thing. – slaakso Jan 26 '18 at 14:59

Group by functioning in sql

1 Answers1

Linked