Get last id or date inserted into a mysql table

Navigation
1

I have a history of conversations that are registered in the table sql "conversation" and I want to pick who was the last person who inserted a message in the conversation to know if it was the user who wrote last or if it was the admin (id 28) .

Is there a way to do a SELECT with a rule to know which last date or last id? 

id|idusuario|idconversa|  mensagem  |data
1 | 700     |    2     | mensagem 1 |2018-05-24 10:20:21
2 | 28      |    2     | mensagem 2 |2018-05-24 10:30:21

I've set up the following: 

SELECT idusuario
FROM conversa
GROUP BY idconversa

Then when I return the last id that inserted the conversation I will make an if to say if it was the admin or the user (this part is already ok);

    
asked by anonymous 24.05.2018 / 16:19

3 answers

2

The idea is that you bring it all at once? The code below brings the most current date, grouping by the id of the conversation and the user: 

SELECT idconversa, idusuario, max(data)
FROM conversa 
GROUP BY idconversa, idusuario

If you just want a specific conversation, you would not have to return idconversa nor group by that field, so: 

SELECT idusuario
FROM conversa 
WHERE idconversa = 2 --aqui ficaria o parâmetro de entrada (id da conversa pesquisada)
ORDER BY data desc
LIMIT 1
    
24.05.2018 / 16:30
0

You will sort in a decreasing way. 

SELECT idusuario
FROM conversa
GROUP BY idconversa
ORDER BY ID DESC
LIMIT 1;
    
24.05.2018 / 16:25
0

The following SQLs were made for Oracle, but can be tailored for MySQ.

- filled table

select id, idusuario, idconversa, message, to_char (data, 'dd / mm / yyy hh24: mi: ss') from tb_conversa; 

    ID  IDUSUARIO IDCONVERSA MENSAGEM             TO_CHAR(DATA,'DD/M

     1        700          2 m1                   24/05/018 10:49:51
     2         28          2 m2                   24/05/018 14:50:25
     3         30          2 m3                   24/05/018 12:50:51
     4          1          1 m4                   24/05/018 12:51:54
     5        700          1 m5                   24/05/018 12:52:11
     6         28          1 m6                   24/05/018 14:52:28

- Picking the maximum dates per conversation

select idconversa, max (to_char (data, 'dd / mm / yyy hh24: mi: ss')) data_max from tb_conversa group by idconversa;

IDCONVERSA DATA_MAX 

     1 24/05/018 14:52:28
     2 24/05/018 14:50:25

- bringing the lines that meet the maximum date, per conversation

select t1. * from tb_conversa t1, ( select idconversa, max (to_char (data, 'dd / mm / yyy hh24: mi: ss')) data_max from tb_conversa group by idconversa ) t2 where t1.idconversa = t2.idconversa and to_char (t1.data, 'dd / mm / yyy hh24: mi: ss') like t2.data_max; 

    ID  IDUSUARIO IDCONVERSA MENSAGEM             DATA    

     6         28          1 m6                   24/05/18
     2         28          2 m2                   24/05/18

I'm sorry for the formatting, I'm new to StackOverFlow.

    
24.05.2018 / 17:08
Error getting data from input radio Query in same table