0

i need to select. the most recent item from mysql table. I found several suggestions but nobody was the right one for my case.

Just to recap i have a table with id, itemnr, date_edited

I tried with:

select id, itemnr, max(date_edited) as date_edited from table group by itemnr

the result is the list of item, grouped by itemnr, but not the last one (so where the date_edited is the most recent)

where is the problem?

Mysql database:

CREATE TABLE `table` (
  `id` int(11) NOT NULL,
  `itemnr` varchar(255) CHARACTER SET latin1 NOT NULL DEFAULT 'TBD',
  `date_edited` datetime NOT NULL
 COLLATE latin1_general_ci NOT NULL DEFAULT '0' COMMENT '0 = created, 1=modified, 2=deleted',
  `csc` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
user3783243
  • 5,368
  • 5
  • 22
  • 41
marco77sa
  • 1
  • 4
  • You need to use an `order by` to order the results. I wouldn't use the `max` nor `group by` here. `select id, itemnr, date_edited from table order by date_edited desc limit 1` – user3783243 Jan 17 '22 at 13:53
  • `ORDER BY id DESC LIMIT 1` I think is what you are after – RiggsFolly Jan 17 '22 at 13:56
  • @ RiggsFolly no, order by id is not the recent date i have in the column data_edited. I can change that value by not changing the id... – marco77sa Jan 17 '22 at 13:59
  • @user3783243 what i have now is only 1 record... and not all the list ordered. – marco77sa Jan 17 '22 at 14:00
  • @marco77sa The question is you want to order the data by the date, no? If not please clarify. – user3783243 Jan 17 '22 at 14:08
  • @ user3783243 i have a list of different items (also with the same itemnr but with different date). What i need is a list with distinct itemnr considering the most recent date... – marco77sa Jan 17 '22 at 14:10
  • Can you please post sample data? – user3783243 Jan 17 '22 at 14:35
  • Your current query is against the sql standard and is only allowed because you do not have only full group by sql mode set in mysql. You really should set this sql mode, if you are not familiar with the use of group by! The answers to the duplicate question explain the problem in greater detail and also suggest various solutions. – Shadow Jan 17 '22 at 15:38

0 Answers0