How to select the last rows added in a mysql table

Asked

Viewed 399 times

0

I have the following table sir_ter_conversa which is a conversation history.

id|idterreno|idincorporadora|idusuario|msg|data
1 |    1    |      771      |   771   | a |2018-05-27 10:20:00
2 |    1    |      771      |   773   | b |2018-05-27 10:30:00
3 |    1    |      771      |   771   | c |2018-05-27 11:20:00
4 |    1    |      771      |   773   | d |2018-05-27 11:35:00
5 |    2    |      772      |   775   | e |2018-05-27 13:25:00

idterreno is what groups conversations, id embed is the client, id user is who wrote the message that can be the user or admin (id 773).

I need to make a query that brings the grouped conversations and who made the last interaction, based on the last id or date. In this case it would bring:

id|idterreno|idincorporadora|idusuario|msg|data
4 |    1    |      771      |   773   | d |2018-05-27 10:20:00
5 |    2    |      772      |   775   | e |2018-05-27 10:30:00

I tried the option below, but the problem is that it did not bring the last id you entered, it brought the first id.

SELECT idterreno, idusuario, max(data)
FROM sir_ter_conversa
GROUP BY idterreno

In this consultation he brings Idusuario 771 and not 773:

idterreno|idusuario|max(data)
    1    |   771   |2018-05-27 10:20:00
    2    |   775   |2018-05-27 10:30:00
sql select

  • is coincidence or the higher the id greater the data ALWAYS?

  • yes, the biggest id will always be the biggest date

  • adds the ORDER BY id desc to see if it solves :P

  • 1

    the greatest id will always be the greatest date, not the greatest of all, will be the greatest within each "idterreno"

  • I did it, but the problem is that it brings idusuario from the first line added, not from the last

3 answers

3


I believe this consultation should bring you what you need:

SELECT C1.idterreno, C1.idusuario, C1.data
FROM sir_ter_conversa C1
WHERE C1.id = (SELECT max(C2.id) FROM sir_ter_conversa C2 where C2.idterreno = C1.idterreno)
GROUP BY C1.idterreno, C1.idusuario

1

Assuming you have a table like:

CREATE TABLE sir_ter_conversa
(
    id INTEGER PRIMARY KEY,
    idterreno INTEGER,
    idincorporadora INTEGER,
    idusuario INTEGER,
    msg TEXT,
    data DATE
);

Containing the data:

INSERT INTO sir_ter_conversa ( id, idterreno, idincorporadora, idusuario, msg, data ) VALUES
( 1, 1, 771, 771,'a', '2018-05-27 10:20:00' ),
( 2, 1, 771, 773, 'b', '2018-05-27 10:30:00' ),
( 3, 1, 771, 771, 'c', '2018-05-27 11:20:00' ),
( 4, 1, 771, 773, 'd', '2018-05-27 11:35:00' ),
( 5, 2, 772, 775, 'e', '2018-05-27 13:25:00' );

In Postgres, you can use a combination of LIMIT with the ORDER BY DESC, let’s see:

SELECT
  *
FROM
  sir_ter_conversa
ORDER BY
  id DESC
LIMIT
   2;

Exit:

| id | idterreno | idincorporadora | idusuario | msg |       data |
|----|-----------|-----------------|-----------|-----|------------|
|  5 |         2 |             772 |       775 |   e | 2018-05-27 |
|  4 |         1 |             771 |       773 |   d | 2018-05-27 |

Sqlfiddle: http://sqlfiddle.com/#! 17/95e4c/3

0

You can try it this way, I believe it will help you:

SELECT idterreno, idusuario, data
FROM sir_ter_conversa
GROUP BY idterreno ORDER BY data DESC;

mysql> SELECT id, idterreno, idusuario, data FROM teste GROUP BY idterreno ORDER BY data DESC;
+------+-----------+-----------+---------------------+
| id   | idterreno | idusuario | data                |
+------+-----------+-----------+---------------------+
|    5 |         2 |       775 | 2018-05-24 14:39:00 |
|    1 |         1 |       771 | 2018-05-24 14:37:52 |
+------+-----------+-----------+---------------------+

Browser other questions tagged sql select

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