how to exclude rows that have duplicates in one field

Question

Hello

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 !!!

Answer 1

Not tested, but should work:

; WITH cte AS (
   SELECT
      *
      ,ROW_NUMBER() OVER (PARTITION BY cat_id ORDER BY id DESC) AS RowNum
)
SELECT
   *
FROM cte
WHERE RowNum = 1;

Greets Flo

Answer 2

This query will select the first article (lowest id) from each category

SELECT a.* FROM Article a LEFT JOIN ON
   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.

Answer 3

Try this:

select article.*
    from article
        join (select min(id) as id, cat_id from article group by cat_id) a2
            using (id);

or:

select *
    from article
    where id in (select min(id) from article group by cat_id);

Both select one article id (using min()) for each distinct cat_id and select only the records with these ids.