Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
hugor99
Starting Member
5 Posts |
 Posted - 2004-11-10 : 18:25:23
|
| hi,here is my problem:I have a category table and a content table. For each category, there are multiple instances of content. Content has summary, maintext, rank.What I want to do is bring back the top ranked content for each category...Here are what my tables look like (without going into the gory details):Category TableCategoryIDCategoryNameContent TableContentIDCategoryIDContentNameContentSummaryContentRankContentMainTextContentRank is an integer value. I want to bring back the content with the lowest rank for EACH category and have it all in one recordset.Thanks for your help. |
|
|
dsdeming
479 Posts |
Posted - 2004-11-11 : 08:33:31
|
| Try something like this:SELECT ca.CategoryID, ca.CategoryName, co.ContentID, co.ContentName, co.ContentSummary, co.ContentMainText, co.ContentRank FROM Category ca JOIN Content co ON ca.CategoryID = co.CategoryID JOIN ( SELECT CategoryID, ContentRank = MIN( ContentRank ) FROM Content GROUP BY CategoryID ) a ON a.CategoryID = co.CategoryID AND a.ContentRank = co.ContentRankDennis |
 |
|
|
hugor99
Starting Member
5 Posts |
Posted - 2004-11-11 : 12:49:19
|
| Thanks very much. That looks like exactly what I'm looking for. |
|
|
|
|
|
|
Bringing back Top 1 rows from a joined table.