Get last ID or date inserted in a Mysql table

Asked

Viewed 393 times

1

I have a history of conversations that are recorded in the table sql "conversation" and I want to pick up 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).

Have some way to make a SELECT with a rule to know the 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 rode the next:

SELECT idusuario
FROM conversa
GROUP BY idconversa

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

sql select

3 answers

2


The idea is that you bring everything 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 wouldn’t need to return the idconversa nor group by this field, thus:

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

  • that, the idea is to bring all the conversations together, with the date and idusuario of the last line added. I did it this way, but he’s bringing the first line and not the last... :/

  • @Leandromarzullo gives a balcony if solved, I changed the answer

  • 1

    Good! worked out, max(data) solved, thanks!

0

You will order downward.

SELECT idusuario
FROM conversa
GROUP BY idconversa
ORDER BY ID DESC
LIMIT 1;
  • 1

    I did, but he brought the id 1, not the last

0

The Sqls below were made for Oracle, but can be adapted for Mysq.

--table filled

select id, idusuario, idconversa, mensagem, 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 up the maximum dates per conversation

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

IDCONVERSA DATA_MAX 

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

--bringing the lines that meets the maximum date, by conversation

select t1. * from tb_conversa t1, ( select idconversa, max(to_char(date, 'dd/mm/yyy hh24:mi:ss')) data_max from tb_conversation 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

Sorry about the formatting, I’m new to Stackoverflow.

Browser other questions tagged sql select

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