I have a very simple task, but I cannot find any solution. I have two tables, 'articles' and 'categories'
My article table look like this:
id | cat_id | title | content
1 1 Blah Content 1
2 1 Blah2 Content 2
3 2 Blah3 Content 3
My categories table look like this:
id | title
1 Category 1
2 Category 2
You see I have 2 articles that have the same cat_id. I do not want with duplicate cat_id field. I cannot use DISTINCT, because I will get all articles, because I want all fields out.
so if i use DISTINCT like this:
SELECT DISTINCT a.id, a.cat_id, a.title, a.content FROM articles AS a
I will get everything out, but I want output like this
id | cat_id | title | content
2 1 Blah2 Content 2
3 2 Blah3 Content 3
Can someone help me please !!!
This query will select the first article (lowest id) from each category
SELECT a.* FROM Article a LEFT JOIN
Article a2 ON a.id<a2.id AND a.cat_id=a2.cat_id
WHERE a2.id IS NULL
It does an outer join with all other articles. The join clause only joins articles with the same category and with a smaller ID. When there are no matches (a2.id is NULL), then we have the article with the lowest ID for that category.