Home / Questions / 24376
Accepted
El Cóndor
Asked: 2020-09-20 17:38:10 +0800 CST 2020-09-20 17:38:10 +0800 CST

How to select records of the most recent date with MySQL?

  • 772

I have table1 with the following records:

campo1    campo2     campo3

Adrian      25       2016-09-17
Juan        27       2016-09-14
Alex        23       2016-09-03
Ana         31       2016-09-17
Rocio       29       2016-09-17

In the supposed case that field3 is always being modified but not all the records at the same time, as I can get only the records that have the most recent date, try to do it with max()

Select * from tabla1 where campo3 = max(campo3);

In this case I want to get the following result:

campo1    campo2     campo3

Adrian      25       2016-09-17
Ana         31       2016-09-17
Rocio       29       2016-09-17

but I repeat that date again, it will not be fixed, it will always be modified, so try to obtain the most recent date with max(), I already tried to group and nothing, if you are so kind to explain to me what I am failing or what would be the logic that applies

mysql

4 Answers

  1. Best Answer
    Select * from tabla1
WHERE campo3  = (
    SELECT MAX(campo3)
    FROM tabla1
)
    • 4

  2. The query would look like this:

    SELECT * from tabla1
WHERE campo3  = (
    SELECT MAX (campo3)
    FROM tabla1
)
    • 3

  3. If you have several with the same idand you only want to get the largest:

    $sql_semaforo = mysqli_query($conection, "SELECT * FROM `citas_pro`WHERE id_cliente= $data[0] AND fecha_proxima = (SELECT MAX(fecha_proxima) FROM `citas_pro` WHERE `id_cliente` = $data[0])");
    • 0

  4. Given the table:

    CREATE TABLE repetidosTimestamp( c1int(11) NOT NULL DEFAULT '0', c2varchar(100) DEFAULT NULL, c3varchar(100) DEFAULT NULL, c4date DEFAULT NULL, PRIMARY KEY ( c1) ) ENGINE=InnoDB DEFAULT CHARSET=latin1

    With the following data:

    1. Adrian 25 2020-08-01
    2. John 23 2020-08-01
    3. Adrian 22 2020-08-20
    4. Peter 24 2020-08-01
    5. August 20 2020-08-01

    this expression:

    Select * from repeatedTimestamp as one WHERE c4 in (SELECT MAX(c4) FROM repeatedTimestamp where one.c2=c2) order by c4 DESC;

    will give you this answer:

    • 3 Adrian 22 2020-08-20
    • 2 John 23 2020-08-01
    • 4 Peter 24 2020-08-01
    • 5 Aug 20 2020-08-01

    which are all the new records, cleaning the old records from the table according to the time field.

    • 0