Search last access record with MYSQL

Asked

Viewed 127 times

2

Suppose I have the table clientes:

--------------------------------------
| ID| Cliente |    Data_Registro     |
|-------------------------------------
| 7 | Joaquim | 2019-07-17 09:45:00  |
|-------------------------------------
| 6 | Timóteo | 2019-07-16 07:42:00  |
|-------------------------------------
| 5 | Timóteo | 2019-07-13 07:42:00  |
|-------------------------------------
| 4 | Joaquim | 2019-07-11 07:42:00  |
|-------------------------------------
| 3 | Gallvão | 2019-07-11 15:42:00  |
|-------------------------------------
| 2 | Gallvão | 2019-07-11 15:41:00  |
|-------------------------------------
| 1 | Gallvão | 2019-06-11 07:00:00  |
--------------------------------------

This table stores the dates and times that clients accessed my system, as I could do an SQL (Mysql) query to be able to list the last accesses of each client, for example:

--------------------------------------
| ID| Cliente |    Data_Registro     |
|-------------------------------------
| 7 | Joaquim | 2019-07-17 09:45:00  |
|-------------------------------------
| 6 | Timóteo | 2019-07-16 07:42:00  |
|-------------------------------------
| 3 | Gallvão | 2019-07-11 15:42:00  |
--------------------------------------

I tried to use DISTINCT only that the date disturbs me displaying more than one result per customer.

I tried with the following SQL but did not return the last date.

SELECT * FROM clientes GROUP BY Cliente ORDER BY id DESC
mysql query distinct

2 answers

7


Use the GROUP BY clause with the MAX aggregation function:

SELECT Cliente, MAX(Data_Registro) 
FROM clientes
GROUP BY Cliente
ORDER BY Cliente;

  • Complementing the reply of friend @anonimo, I put the ID, Client and Data in the query: SELECT id,
 Cliente,
 max(Data_Registro) AS Data_Registro
FROM SuaTabela 
 GROUP BY Cliente;

  • I was doing the answer, but @anonimo was faster, so you can test the solution online, follows example

  • Perfect! It worked beauty! I forgot this MAX()

  • Wonderful, thank you! It worked.

  • @Edvaldo Lucena: just making it clear that although this construction works on Mysql it does not comply with the SQL-92 standard and does not work on other DBMS.

  • Oracle works, and I believe it meets SQL-92 @aninomo

  • As long as there is a functional dependency, which is not the case of ID and Client above.

  • @anonimo did not understand your statement, but as you managed to help the colleague, it is ok.

  • 1

    See documentation: https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html

  • anonimo, can you answer my question? https://answall.com/questions/469237/identificar-registros-sequentialof each user

  • can you answer my question? https://answall.com/questions/469237/identificar-registrations-sequentialof each user

Show 6 more comments

0

From what I understand, you want to do a consultation of the last customers who had access

You could try doing it this way 

select Cliente, Data_Registro from *tabela* where (select max( Data_Registro) from *tabela*) group by Cliente

hope I’ve helped.

Browser other questions tagged mysql query distinct

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