|
|
Posted by nick on 09/14/07 21:39
Hi Hugo,
Thanks for help, just what I needed. I was trying to put a 'top 1' in
the nested select which obviously (now!) didn't work..
Nick.
> Hi Nick,
>
> That's only possible if you somehow specify WHICH of the categories you
> want. Lowest CategoryID? Longest CategoryName? CategoryID closest to a
> multiple of 42?
>
> I'm sure that you don't care, but since SQL Server has no "just give me
> any I don't care which" operator, you'll have to specify something.
>
> Assuming you want the lowest CategoryID, you can use the following on
> SQL Server 7.0 and up (on SQL 2005, you might find a better solution
> with CTEs and CROSS APPLY, but since you failed to specify the version,
> I'll play it safe):
>
> CREATE VIEW YourShinyNewView
> AS
> SELECT r.ResourceID, r.ResourceName,
> c.CategoryID, c.CategoryName
> FROM Resource AS r
> INNER JOIN (SELECT Resource, MIN(CategoryID) AS MinCategoryID
> FROM ResourceCategory
> GROUP BY Resource) AS rc
> ON rc.ResourceID = r.ResourceID
> INNER JOIN Category AS c
> ON c.CategoryID = rc.MinCategoryID;
>
> (Untested - seewww.aspfaq.com/5006if you prefer a tested reply)
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
Navigation:
[Reply to this message]
|