CODEWITHSUNDEEP
mysql
version 2
version 2

Reputation: 1059

Unable to get the latest value added to Mysql table based on posted time

SELECT * FROM discussion_comments GROUP BY disc_id ORDER BY posted_date DESC

I have table example like given below:

CREATE TABLE example
(
id int(11),
cname varchar(11),
posted_date date,
posted_time varchar(20)
);

with values like:

INSERT INTO example
VALUES 
(1,'abc','2015-03-26','04:25 PM'),
(1,'def','2015-03-27','04:30 PM'),
(2,'ghi','2015-03-11','02:25 AM'),
(2,'jkl','2015-03-15','12:25 PM');

and I am trying to get only the latest value added to the table for an id based on posted_date & posted_time fields.

The result I am trying to achieve is:

(1,'def','2015-03-27','04:30 PM')
(2,'jkl','2015-03-15','12:25 PM')

The query I tried is as follows:

SELECT * FROM `example GROUP BY id ORDER BY posted_date DESC

I am not getting the desired result. Where did I go wrong??

Upvotes: 0

Views: 42

Answers (2)

Raj
Raj

Reputation: 1965

if you want to sort desc date + time

select * from (select * from example order by STR_TO_DATE(CONCAT(posted_date,posted_time),'%Y-%m-%d%h:%i %p') desc) as xx group by id;

If you want to sort desc by just date

SELECT * FROM (select * from example order by posted_date desc) as ex group by id

IMHO storing as timestamp is better when dealing with date operations unless maybe you have more than one timestamp fields is same table.

Upvotes: 1

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44864

There are many ways and one way is left join

select e1.* from example e1 
left join example e2 on e1.id = e2.id 
and e1.posted_date < e2.posted_date where e2.id is null;

Or Uncorrelated Sub-query 

select e1.* from example e1 
join ( 
   select id,max(posted_date) as posted_date from example group by id 
)x 
on x.id = e1.id and x.posted_date = e1.posted_date ;

Upvotes: 1

Related Questions