I have the tables, Widgets and Categories, and a table WidgetCategories that expresses the many-to-many relationship...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
between a Widget and a Category. Each widget has a 'price' field. I want to write a query that, given a category id, returns the most expensive widget of that category. I end up wanting to do something like (pardon the pseudo-SQL):
tempTable := SELECT widgetID, price FROM Widgets, WidgetCategories WHERE Widgets.widgetID = WidgetCategories.widgetID AND WidgetCategories.categoryID = $given_category$
where $given_category$ is the category I'm interested in, followed by
SELECT widgetID, price FROM tempTable WHERE price = MAX(price)
My solution seems very awkward, with the creation of a temp table. Is there any way to do this better?
You're almost there. Just make your temp table query a subquery of the retrieval query:
select Widgets.widgetID , price from Widgets , WidgetCategories where Widgets.widgetID = WidgetCategories.widgetID and WidgetCategories.categoryID = $given_category$ and price = ( select max(price) from Widgets , WidgetCategories where Widgets.widgetID = WidgetCategories.widgetID and WidgetCategories.categoryID = $given_category$ )
Note that both the outer query and subquery need to check for the selected category.
For More Information
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.