Jump to content

Select Only Unique Entries On A Specific Field


gdfhghjdfghgfhf
 Share

Recommended Posts

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 :/

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.