junkone junkone - 2 months ago 20
MySQL Question

MYSQL error 1064 ,how to fix it?

I am using MySQL and have a sql error. I need to have distinct columns for datetime and for every distinct datetime, I want to show the max impact_id

select max(impact_id),distinct datetim
from news
where impact_id is not null and
datetim is not null

group by datetim
order by datetim asc

I am getting error

SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct datetim from news where impact_id is not null and


datetim is not null'

CREATE TABLE `news` (
`datetim` DATETIME NULL DEFAULT NULL,
`symbol` VARCHAR(50) NULL DEFAULT NULL,
`impact` VARCHAR(50) NULL DEFAULT NULL,
`_id` MEDIUMINT(9) NOT NULL AUTO_INCREMENT,
`impact_id` INT(11) NULL DEFAULT NULL,
PRIMARY KEY (`_id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=3072
;

Answer

select distinct max(impact_id),datetim from news where impact_id is not null and datetim is not null group by datetim order by datetim asc