Select Only Unique Entries On A Specific Field

[gdfhghjdfghgfhf]

Here's my SQL structure:

 

[category] [article_id] [name]

1 27487 dog

2 445343 cat

3 445345 fish

1 43253 horse

2 543435 bird

 

i'm trying to query only the entries who have unique category ID. With the above example i would expect this result:

 

[category] [article_id] [name]

1 27487 dog

2 445343 cat

3 445345 fish

 

 

How can i do that ? I tryed using DISTINCT and GROUP BY, looks easy to do when you have only 1 field but i don't know how to use it in my situation... If i use DISTINCT it will look for unique rows on the 3 fields... I want to look for unique rows only for "category" field (the entry shouldnt be considered as unique as soon as the category ID has been found in another entry previously)

 

I hope you can understand what i'm trying to do :/

[awjudd]

But that list isn't a list of entries with a unique category ID. 1 matches dog and horse, while 2 matches cat and bird. If anything, the query you are talking about should only return "bird".

 

One way you could do it is but not 100% correct ...

 

SELECT c.category, c.article_id, c2.name
FROM ( SELECT category,MIN(article_id) AS article_id FROM categories GROUP BY category ) AS c
JOIN categories AS c2 ON c.category = c2.category AND c.article_id = c2.article_id

 

This will get you the MINIMUM article id for each category and then you can do your match on it.

 

~awjudd

[kicken]

You can use a GROUP BY category clause to reduce the results down to unique category ID's. Mysql will just choose an arbitrary row to pull the article_id and name fields from so there is no guarantee that you'd get 'dog' and 'cat' instead of 'horse' and 'bird' (or any combination there of).

 

Example

 

That is not really the right way of using GROUP BY though. Perhaps if you explain a bit better what kind of task you're trying to accomplish we could help determine the best route to take.

 

[Barand]

or you could expand on the group by and add group_concat

 

SELECT category, GROUP_CONCAT(name) as names
FROM mytable
GROUP BY category

 

whichshould give

 

1 | dog,horse

2 | cat,bird

3 | fish