Q
Problem solve Get help with specific problems with your technologies, process and projects.

Return only one product per category

I have two tables, one listing categorization information, and another listing product information. They can be joined by a category_id column that is present in both tables. Naturally there are multiple rows in the product table for each category_id. I would like to perform a simple join but returning only one product per category rather than all products with the duplication of category information.

I have two tables, one listing categorization information, and another listing product information. They can be...

joined by a category_id column that is present in both tables. Naturally there are multiple rows in the product table for each category_id. I would like to perform a simple join but returning only one product per category rather than all products with the duplication of category information. Also, some categories do not have any associated products, so I imagine a LEFT OUTER JOIN should be used. Hope this is clear, otherwise please let me know.

Very clear. And you were right about the LEFT OUTER JOIN.

select c.category_id
     , c.category_name
     , p.product_id
     , p.product_name
  from categories as c
left outer
  join products as p
    on c.category_id = p.category_id
   and p.something 
     = ( select max(something)
           from products
          where category_id = c.category_id )

What's the "something" column? Your call. Pick a column for which you know there will be only one highest value. The product primary key suggests itself. A "date_added" timestamp will also work. In data modelling terminology, you can use any candidate key for this purpose.

This was last published in October 2005

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close