Hi Guys
I have a table of news articles with 5 columns - id (primary key, unique), article_id, status (int 0 or 1),date(timestamp), and content
Every time an article is updated, a new row goes into the table with the same article_id as the original article, but a new date created and status=0 till it's approved(simple versioning then)
So for example
id|article_id|status|date| content
1 1 1 20030320160529 original article 1
2 1 1 20030320160530 updated article 1
3 1 0 20030320160531 updated article 1
4 2 1 20030320160430 article 2
5 3 1 20030320160630 article 3
I want to return the latest approved version of each article, sorted by date descending, so in the case above I want ids
5
2
4
in that order.
I guess I'll need some sort of groupwise maximum query as per or even a version of the clever example submitted by a user below it - Csaba Gabor. But the closest I have come is to retrieve the distinct article_ids but the order is still wrong, using
SELECT article.article_id FROM article LEFT JOIN article AS a2 ON article.article_id = a2.article_id AND article.date < a2.date WHERE a2.article_id IS NULL AND status='1' ORDER BY article.date;
Wrong because the query is ignoring the status='1' part
Can anyone assist?
cheers
I have a table of news articles with 5 columns - id (primary key, unique), article_id, status (int 0 or 1),date(timestamp), and content
Every time an article is updated, a new row goes into the table with the same article_id as the original article, but a new date created and status=0 till it's approved(simple versioning then)
So for example
id|article_id|status|date| content
1 1 1 20030320160529 original article 1
2 1 1 20030320160530 updated article 1
3 1 0 20030320160531 updated article 1
4 2 1 20030320160430 article 2
5 3 1 20030320160630 article 3
I want to return the latest approved version of each article, sorted by date descending, so in the case above I want ids
5
2
4
in that order.
I guess I'll need some sort of groupwise maximum query as per or even a version of the clever example submitted by a user below it - Csaba Gabor. But the closest I have come is to retrieve the distinct article_ids but the order is still wrong, using
SELECT article.article_id FROM article LEFT JOIN article AS a2 ON article.article_id = a2.article_id AND article.date < a2.date WHERE a2.article_id IS NULL AND status='1' ORDER BY article.date;
Wrong because the query is ignoring the status='1' part
Can anyone assist?
cheers