Group by functioning in sql [duplicate]

Question

This question already has answers here:

MySQL - Selecting a Column not in Group By (4 answers)

SQL select only rows with max value on a column [duplicate] (27 answers)

Closed 2 years ago.

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!

Answer 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.