how to exclude rows that have duplicates in one field

Question

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

Sorry - I don't see how ARTICLE.id values 1 and 2 are the same article. The id's are different, as are the titles... — OMG Ponies, Jul 26 '10 at 20:40
How do you decide which of the two rows for cat 1 that you want? — Tom, Jul 26 '10 at 20:41

score 0 · Answer 1 · edited Dec 12 '11 at 20:02

0

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;

edited Dec 12 '11 at 20:02

answered Jul 26 '10 at 20:40

Florian Reischl

3,788
1
24
19

score 0 · Accepted Answer · edited Apr 11 '13 at 12:29

0

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.

edited Apr 11 '13 at 12:29

Maks3w

6,014
6
37
42

answered Jul 26 '10 at 20:41

mdma

56,943
12
94
128

I have explained as best I can in the paragraph after the query. I can try to elaborate - which part are you having trouble with? – mdma Jul 26 '10 at 20:57

thieger · Answer 3 · 2010-07-27T12:12:32.863

0

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.

edited Jul 27 '10 at 12:12

answered Jul 26 '10 at 20:59

thieger

146
5

how to exclude rows that have duplicates in one field

3 Answers3