|
|
Posted by Hugo Kornelis on 11/23/06 23:17
On 23 Nov 2006 01:48:32 -0800, othellomy@yahoo.com wrote:
>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...
Hi othellomy,
Though you can use ORDER BY in a subquery if you also use TOP, the ORDER
BY will only be used to determins which rows meat the TOP criterium;
there is no guarantee that the actual order of the query will be the
same. In fact, SQL Server 2005 will ignore both TOP 100 PERCENT and the
accomanying ORDER BY, since it is essentially a no-op to restrict the
output to 100 percent of the regular output.
If you really want to move the DISTINCT to a subquery (which in this
case is NOT needed - see my reply to Nobody), you could use
SELECT a.Name
FROM (SELECT DISTINCT c.Name, c.DisplayOrder
FROM Categories AS c
INNER JOIN Items AS i
ON i.CategoriID = c.CategoryID
WHERE i.BrandID = 1) AS a
ORDER BY a.DisplayOrder;
(untested)
--
Hugo Kornelis, SQL Server MVP
Navigation:
[Reply to this message]
|