|
|
Posted by othellomy on 11/23/06 09:48
Okay my mistake. This will do the job:
select a.tblCategories.Name
from (select DISTINCT top 100 percent tblCategories.Name,
tblCategories.DisplayOrder
from tblCategories
INNER JOIN tblItems
on tblCategories.CategoryID = tblItems.CategoryID
where BrandID=1 order by
tblCategories.Name,tblCategories.DisplayOrder) a
Unlike other databases, SQL Server does not allow 'order by' within
derived tables, so had to use top etc...
othellomy@yahoo.com wrote:
> Just remove the order by ie:
>
> select DISTINCT tblCategories.Name, tblCategories.DisplayOrder
> from tblCategories
> INNER JOIN tblItems
> on tblCategories.CategoryID = tblItems.CategoryID
> where BrandID=1
> /* order by tblCategories.DisplayOrder */
>
> Nobody wrote:
> > I'm trying to write a stored proc...
> >
> > Basically, I have a tblItems table which contains a list of every item
> > available. One of the columns in this table is the brand... for test
> > purposes, I hardcoded the BrandID=1...
> >
> > tblItems also contains a category column (int) which contains a categoryID
> > of 0..3...
> >
> > I then have a category table which has CategoryID, Name, and DisplayOrder...
> >
> > So basically what I'm trying to do is return a list of Category NAMES that
> > have items in them for a specifc brand... but I want to sort the returned
> > categories by the DisplayOrder column...
> >
> > this is what I have now:
> >
> >
> > select DISTINCT tblCategories.Name, tblCategories.DisplayOrder from
> > tblCategories
> > INNER JOIN tblItems
> > on tblCategories.CategoryID = tblItems.CategoryID
> > where BrandID=1 order by tblCategories.DisplayOrder
> >
> > this does what I want it to do, but its returning TWO columns... Name AND
> > DisplayOrder... I only want to return Name, but if I take the DisplayOrder
> > out of the select portion, it errors out because it can't order by that...
> >
> > Any ideas? Obviously I need the DISTINCT keyword so I dont get 10 copies of
> > the same category name.
Navigation:
[Reply to this message]
|