How to select the last lines added in a mysql table

Question

How to select the last lines added in a mysql table

Navigation

#1 by (3 votes)
#2 by (1 votes)
#3 by (0 votes)

0

I have the following table sir_ter_conversa which is a history of conversations.

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 the conversations, incorporating id is the client, user id is who wrote the message that can be the user or the admin (id 773).

I need to make a query that brings the grouped conversations together and who made the last interaction based on the last id or date. In that 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 that you entered, brought the first id.

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

In this query it 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

asked by anonymous 24.05.2018 / 19:24

3 answers

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 ORDER BY DESC , let's see:

SELECT
  *
FROM
  sir_ter_conversa
ORDER BY
  id DESC
LIMIT
   2;

Output:

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

SQLFiddle: link

24.05.2018 / 20:18

0

You can try it that 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 |
+------+-----------+-----------+---------------------+

24.05.2018 / 19:50

PHP: Function as condition for an if android.permission.MODIFY_PHONE_STATE - How to assign system permission?

score 3 · Accepted Answer

I believe this query should bring 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