Pick up a user’s first and last entry

Asked

Viewed 32 times

1

I have the following query:

Sqlfiddle

The question is as follows. In this table there are several interactions of several users in the same transaction. What I need is to take the last user’s duration time in the transaction.

For example:

According to the above table, the idusers 560049 was the last user to fulfill the transaction. He started the service 2018-09-12T09:34:50Z and ended the service 2018-09-12T09:54:50Z. The service time in this case is 20 minutes.

What matters to me is always the last user of the transaction.

If I try to put min and max gives problem because it groups and gets wrong.

And if it is the case that the user does not have an end time, I just want to ignore it. As in the example below:

Sqlfiddle

I know the question is complex, but I couldn’t explain it any other way. Should any moderator find it unclear, let me know.

mysql

  • A ORDER BY with LIMIT 1 would not solve? select * from obusuario
order by created desc limit 1 ?

  • No, because I need to take the attendance time. That is, the first insertion and the last. :)

  • Try it this way: SELECT idusers, MIN(created), MAX(created)
FROM obusuario
WHERE idusers IN (SELECT idusers FROM obusuario
ORDER BY created DESC LIMIT 1)

  • Gave this error: com.mysql.jdbc.exceptions.jdbc4.Mysqlsyntaxerrorexception: This version of Mysql doesn’t yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

  • Run in your bank? Because fiddle is LIMIT locked in subquery

  • Yes, it was in my bank. I am using dbeaver. And I need that information to be displayed in the Tabase bi, and gave the same error there tbm. :(

Show 1 more comment

1 answer

0


One way to do it (which worked due to Mysql limits), would be by subquery:

SELECT * 
FROM (SELECT idusers, MIN(created) mn, MAX(created) mx 
FROM obusuario 
GROUP BY idusers 
ORDER BY MAX(created)) tab 
ORDER BY mx DESC LIMIT 1

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.