How Affect Group By to Other Second Join Table-CodePudding

I have some table like this

table request_buys

| id | invoice           | user_id |
| -- | ----------------- | ------- |
| 3  | 20220405/01104298 | 1       |

table traces

| id | request_buy_id | status_id | created_at          |
| -- | -------------- | --------- | ------------------- |
| 37 | 3              | 1         | 2022-03-27 14:12:25 |
| 38 | 3              | 2         | 2022-03-28 14:12:25 |
| 39 | 3              | 3         | 2022-03-29 14:12:25 |
| 40 | 3              | 4         | 2022-03-30 14:12:25 |
| 41 | 3              | 5         | 2022-03-31 14:12:25 |
| 42 | 3              | 6         | 2022-04-01 14:12:25 |

table statuses

| id | nama              |
| -- | ----------------- |
| 1  | Order Placed      |
| 2  | Order Paid        |
| 3  | Accepted          |
| 4  | Picked by Courier |
| 5  | In Transit        |
| 6  | Delivered         |
| 7  | Rated             |
| 8  | Rejected          |
| 9  | Canceled          |

and then i try to design query like below

select
 request_buys.invoice,
 MAX(traces.id) as traces_id,
 MAX(statuses.nama) as statuses_nama
from 
 `request_buys`
 inner join `traces` on `request_buys`.`id` = `traces`.`request_buy_id`
 inner join `statuses` on `traces`.`status_id` = `statuses`.`id`
where
 `user_id` = 1
group by
 request_buys.id

and produces output like the following

output

| invoice           | traces_id | statuses_nama     |
| ----------------- | --------- | ----------------- |
| 20220405/01104298 | 42        | Picked by Courier |

and the output i expect should be like in the table below

expect

| invoice           | traces_id | statuses_nama     |
| ----------------- | --------- | ----------------- |
| 20220405/01104298 | 42        | Delivered         |

I understand my error is in MAX(statuses.nama) which I should change like removing MAX() in statuses.nama

But i just get error like this "SELECT list is not in GROUP BY clause and contains nonaggregated ... this is incompatible with sql_mode=only_full_group_by"

then I tried some to clear the value "ONLY_FULL_GROUP_BY" with a query like the following

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))

and the result is like this

output

| invoice           | traces_id | statuses_nama     |
| ----------------- | --------- | ----------------- |
| 20220405/01104298 | 42        | Order Placed      |

and I'm really stuck at this and how to make trace_id.status_id from the "GROUP BY" result based on request_buys.id still have a relationship with statuses.id

CodePudding user response：

Your problem lies with your misuse of the MAX(statuses.nama) expression. Based on your expected output,you intend to get the statuses.nama which matches the MAX(traces.id), NOT the MAX(statuses.nama) value which returns the highest value in terms of alphabetic order. In this case, the initial letter 'P' > 'D' . I have tweaked your code a bit and tried it on workbench,supposing there are more than one invoice for a particular user.(e.g insert into request_buys values (4,'20230405/01104298',1); insert into traces values (43,4,7,'2022-04-01 14:12:25');) It works as intended.

select invoice, t.id as traces_id, s.nama as statuses_name from request_buys r 
join traces t on r.id=t.request_buy_id
join statuses s on t.status_id=s.id
join
    (select traces.request_buy_id, MAX(traces.id) as traces_id
    from `request_buys`
    inner join `traces` on `request_buys`.`id` = `traces`.`request_buy_id`
    where
    `user_id` = 1
    group by
    traces.request_buy_id ) join_t
    on t.request_buy_id=join_t.request_buy_id and t.id=join_t.traces_id
;

CodePudding user response：

If I'm understanding correctly, you're trying to retrieve the most recent status for each invoice. Using MAX(nama) won't return that result, because it just picks the maximum status name alphabetically.

Assuming you're using MySQL 8.x, use ROW_NUMBER() to sort and rank the statuses for each invoice, by the most recent date first. Then grab the latest one using where rowNum = 1

WITH cte AS (

    SELECT rb.id AS request_buy_id
            , rb.invoice
            , t.id AS traces_id
            , s.nama AS statuses_nama
            , ROW_NUMBER() OVER(PARTITION BY rb.id ORDER BY t.created_at DESC) AS RowNum
    FROM   request_buys rb
             INNER JOIN traces t ON rb.id = t.request_buy_id
             INNER JOIN statuses s ON t.status_id = s.id
    WHERE  user_id = 1
)
SELECT * 
FROM   cte 
WHERE  RowNum = 1
;

Result:

request_buy_id	invoice	traces_id	statuses_nama	RowNum
3	20220405/01104298	42	Delivered	1

db<>fiddle here