Get the most recent record by date in mysql

Question

I have a table in which I store a code, a price, and a date.

Code is type varchar, price is type float (yes, I know it is better to store it as Double), date is type date

create table prices (
 code varchar(15), 
 price float(5),
 date date
)

so,

insert into prices (code,price,date) values
('ADV000001','2.1','2021-02-03'),
('ADV000001','0.3','2021-11-22'),
('ADV000001','20.0','2021-11-23'),
('ADV000001','31.4','2017-01-11'),
('ADV000001','99.99','2012-12-09'),
('ADV000123','31.4','2017-04-21'),
('ADV000123','0.4','2016-12-02'),
('ADV000123','31','2012-11-06'),
('ADV000991','3','2000-01-15'),
('ADV000991','1.4','2004-01-13'),
('ADV000991','0.9','2011-01-30'),
('ADV000991','12','2017-01-23'),
('ADV000991','854.82','2004-04-30'),
('ADV000991','231.11','2009-04-15'),
('ADV000991','242.66','2021-04-09'),
('ADV000912','111.1','2021-01-11'),
('ADV000912','1.4','2020-01-11'),
('ADV031242','75.48','2019-03-11'),
('ADV031242','231.42','2019-07-11'),
('ADV011912','1324.11','1994-11-11'),
('ADV000112','685.04','1993-06-11'),
('ADV000777','757.54','2001-06-11'),
('ADV000777','221.74','2002-09-11'),
('ADV000777','1352.12','2012-04-11'),
('ADV000215','856.81','2011-12-11'),
('ADV000202','511.99','2011-11-11')

I try to make this select, but it doesn't work very well, it brings the correct date, however it brings me any price, not the correct one!!!!

SELECT 
code, price, date 
FROM 
prices 
WHERE 
date = (SELECT MAX(date) FROM prices)

the result:

code       price    date
ADV000001   0.3    2021-11-23

Ideally, it should show each code with its respective prices and the most recent date.

code           price    date
ADV000001      20       2021-11-23
ADV000123      31.4     2017-04-21
ADV000991    242.66     2021-04-09
...          ...          ...

Any ideas? maybe a sub-consult? a group by sentence not work!

The MYSQL MAX() function doesn't seem to work, it doesn't group by codes and only brings me one record. Here is the link where you can quickly ask your queries, I would appreciate it. dbfiddle.uk

score 1 · Accepted Answer · answered Nov 24 '21 at 03:35

Here are your queries, but first!!! It is never a good idea to use SQL keywords as column names. If you do that you must quote tzhe fieldname with backticks.

get one result

SELECT 
CODE, price, DATE 
FROM 
prices 
WHERE 
`date`= (SELECT MAX(DATE) FROM prices);

get all results

SELECT p1.`code`, p1.`maxdate`, p2.`price` FROM (
    SELECT `CODE`, MAX(`DATE`) AS maxdate FROM prices GROUP BY `CODE`) AS p1
LEFT JOIN prices p2 ON p1.`code`= p2.`code`AND p2.`date` = p1.maxdate
ORDER BY p1.`code`;

sample

MariaDB [order]> SELECT 
    -> CODE, price, DATE 
    -> FROM 
    -> prices 
    -> WHERE 
    -> `date`= (SELECT MAX(DATE) FROM prices);
+-----------+-------+------------+
| CODE      | price | DATE       |
+-----------+-------+------------+
| ADV000001 |    20 | 2021-11-23 |
+-----------+-------+------------+
1 row in set (0.06 sec)


MariaDB [order]> SELECT p1.`code`, p1.`maxdate`, p2.`price` FROM (
    -> SELECT `CODE`, MAX(`DATE`) AS maxdate FROM prices GROUP BY `CODE`) AS p1
    -> LEFT JOIN prices p2 ON p1.`code`= p2.`code`AND p2.`date` = p1.maxdate
    -> ORDER BY p1.`code`;
+-----------+------------+---------+
| code      | maxdate    | price   |
+-----------+------------+---------+
| ADV000001 | 2021-11-23 |      20 |
| ADV000112 | 1993-06-11 |  685.04 |
| ADV000123 | 2017-04-21 |    31.4 |
| ADV000202 | 2011-11-11 |  511.99 |
| ADV000215 | 2011-12-11 |  856.81 |
| ADV000777 | 2012-04-11 | 1352.12 |
| ADV000912 | 2021-01-11 |   111.1 |
| ADV000991 | 2021-04-09 |  242.66 |
| ADV011912 | 1994-11-11 | 1324.11 |
| ADV031242 | 2019-07-11 |  231.42 |
+-----------+------------+---------+
10 rows in set (0.01 sec)

MariaDB [order]>

score 0 · Answer 2 · answered Nov 24 '21 at 03:24

0

Make your Select query's "Order by" in descending. I'm talking about the Select query of getting the latest date in your database.

answered Nov 24 '21 at 03:24

Michael

31
1
6

vee · Answer 3 · 2021-11-24T03:39:18.287

If you use where `date` = (SELECT MAX(`date`) FROM `prices`) then it will be select only one latest (MAX) from the table to use in this date condition. Result is showing only 1 record.

Refer from this question

SELECT 
    `prices`.*
FROM 
    `prices`
INNER JOIN (
    SELECT MAX(`date`) AS `maxdate`, `price`, `code`
    FROM `prices`
    GROUP BY `code`
) AS `prices2` 
    ON `prices`.`date` = `prices2`.`maxdate`
ORDER BY `date` DESC

Results:

code	price	date
ADV000001	20	2021-11-23
ADV000991	242.66	2021-04-09
ADV000912	111.1	2021-01-11
ADV031242	231.42	2019-07-11
ADV000123	31.4	2017-04-21
ADV000777	1352.12	2012-04-11
ADV000215	856.81	2011-12-11
ADV000202	511.99	2011-11-11
ADV011912	1324.11	1994-11-11
ADV000112	685.04	1993-06-11

Get the most recent record by date in mysql

3 Answers3