mysql sort by MAX timestamp/date

Forum Moderators: open

Message Too Old, No Replies

mysql sort by MAX timestamp/date

mysql sort max timestamp date

MrJody

9:34 am on Nov 5, 2009 (gmt 0)

Select article by author order by MAX(timestamp)

I would like to return the selected results order by last modified timestamp. Without duplicate article numbers.

I have two tables one with each

+---------+--------+
¦ article ¦ author ¦
+---------+--------+
¦ 0001 ¦ Sam ¦
¦ 0002 ¦ Tom ¦
¦ 0003 ¦ Cindy ¦
¦ 0004 ¦ Val ¦
+---------+--------+

Article History
+---------+--------+---------------------+
¦ article ¦ editor ¦ timestamp ¦
+---------+--------+---------------------+
¦ 0001 ¦ Sam ¦ 2009-01-20 21:25:08 ¦
¦ 0002 ¦ Tom ¦ 2009-03-21 17:54:21 ¦
¦ 0001 ¦ Val ¦ 2009-04-05 12:10:10 ¦
¦ 0002 ¦ Val ¦ 2009-04-08 19:05:59 ¦
+---------+--------+---------------------+

rocknbil

4:17 pm on Nov 5, 2009 (gmt 0)

Welcome aboard MrJody, you don't need to use MAX for date sorting, just use order by with a descending direction.

Edit:this is incorrect, have to run but will return to this later, sorry --> To avoid duplicates, use distinct.

select distinct (articles.article), articles.author, ar_history.editor,ar_history.timestamp
from articles,ar_history where
articles.article=ar_history.article
order by ar_history.timestamp desc;

asc = earliest first, desc = most recent first. You can substitute

from articles,ar_history where articles.article=ar_history.article

with

from articles left join ar_history on articles.article=ar_history.article

Should be synonymous if article always has a value.

MrJody

10:49 pm on Nov 5, 2009 (gmt 0)

Thank you.
That was pretty straight forward.
I'm still getting duplicate article id numbers even when using DISTINCT

LifeinAsia

11:12 pm on Nov 5, 2009 (gmt 0)

You'll need to do something like the following:
SELECT a.Author, b.TimeStamp
FROM articles a INNER JOIN (SELECT article, MAX(timestamp) AS timestamp FROM ar_history GROUP BY article) b ON a.article=b.article