What I don't understand about SQL MAX ()?

Data: (log_time is a type DATETIME)

log_id  | action      | log_time            | user
--------------------------------------------------
1         Processed     2011-02-28 16:38:48   1
2         Processed     2011-03-02 16:56:43   5
3         Processed     2011-03-02 17:00:17   5
4         Processed     2011-03-03 08:59:33   5

Query:

SELECT log_time, user 
FROM logs
WHERE action = "Processed"
GROUP BY action 
HAVING MAX(log_time)

Result:

log_time            | user
--------------------------
2011-02-28 16:38:48   1

It is clear that this is not enough max log_time. If I changed the request to ...

SELECT MAX(log_time), user 
FROM logs 
WHERE action = "Processed" 

Then I get naturally:

log_time            | user
--------------------------
2011-03-03 08:59:33   1

Now, I obviously want the data on line 4: March 3, but user 5. I understand that I can get this by doing a simple one SELECT ... ORDER BY log_time DESC LIMIT 1. But my question is: what am I doing with these MAX()requests, which is wrong? It seems to me that if I performed a query with HAVING MAX()so that it would give me a string that, well, would have max. What I don’t understand about how it works MAX()?

Edit: work out my question, mainly when I see a request ...

SELECT * FROM logs WHERE action = "Processed"
GROUP BY action HAVING MAX(log_time)

... , , , , log_time, . , -, . HAVING MAX() ?

+3
5

, Damien_The_Unbeliever, , , HAVING MAX() . , , .

HAVING MAX(log_time), HAVING 2011-03-03 08:59:33, SQL, , , IF (5). . HAVING - , , .

+1

GROUP BY .

SELECT MAX(log_time), user 
    FROM logs 
    WHERE action = "Processed" 
    GROUP BY user

, , :

SELECT MAX(log_time)
    FROM logs 
    WHERE action = "Processed" 

, , , ,

SELECT l.user, l.log_time
    FROM logs l
        INNER JOIN (SELECT MAX(log_time) as max_time
                        FROM logs 
                        WHERE action = "Processed") q
            ON l.log_time = q.max_time
                AND l.action = "Processed"
+4

MAX (log_time) 1, .

0
SELECT log_time, user 
FROM logs 
WHERE action = "Processed"  && log_time=(select MAX(log_time) from logs)
)

2011-03-03 08:59:33 5

0

HAVING -clause GROUPS, . , , "" - DOES HAVING, MAX (log_time) log_time (, , HAVING MAX(log_time) true). , )...

, SELECT log_time, :

SELECT MAX(log_time), user 
FROM logs
WHERE action = "Processed"
GROUP BY action;

, "1" , , GROUP BY. , MySQL , . 4 . , :

SELECT logs.user, logs.log_time
FROM logs INNER JOIN 
  (SELECT MAX(log_time) as max, action
   FROM logs
   WHERE action = "Processed"
   GROUP BY action) sub ON logs.log_time = sub.max AND logs.action = sub.action

NOTE : The SQL query that you give as an example is not a valid SQL query according to standard SQL. It runs on mysql, but this is due to how MySQL implemented GROUP BY. In standard SQL, the only thing you can choose is the results of the aggregate functions and / or columns mentioned in the GROUP BY clause.

So, in other database systems you cannot select user-column, since it is not GROUP BY-column, and not the result of an aggregate function. In order for it to be valid standard SQL, you need to write:

SELECT MAX(log_time), user 
FROM logs
WHERE action = "Processed"
GROUP BY action, user -- Create groups based on both action AND user.
                      -- This allows us to SELECT the user column unambigiously.
;
0
source

Source: https://habr.com/ru/post/1796068/


All Articles