February 5, 2006 at 8:35 pm
Is there a way to write a SQL SELECT statement that will return an output sorted as follows?
Example table dbo.Categories where CategoryID is PK and MainCatID is a self-referential FK that can only refer to items that have a null MainCatID themselves>
dbo.Categories:
CategoryIDCategoryNameMainCatID
_________________________________________
1Video(null)
2Audio(null)
3Media(null)
4Blank CDs3
5Car Stereos2
6Video Tapes3
7DVD Players1
8MP3 Players2
9DVD Recorders1
10Home Stereos2
The output of the SQL SELECT needs to be in this order:
CategoryNameCategoryIDMainCatID
_________________________________________
Audio2(null)
Car Stereos52
Home Stereos102
MP3 Players82
Media3(null)
Blank CDs43
Video Tapes63
Video1(null)
DVD Players71
DVD Recorders 91
Indenting in the CategoryName column is for clarity only.
The actual column output does not need to be formatted.
Doug
February 6, 2006 at 9:33 pm
Try this :
select c.CategoryName, c.CategoryID, c.MainCatID, isnull(m.CategoryID, c.CategoryID) as MCatID
fromCategories c left join Categories m
on c.MainCatID= m.CategoryID
order by MCatID, c.CategoryID
February 6, 2006 at 11:16 pm
Many thanks for your reply.
This returns the sub-categories sorted by ID within each main category, and with the main categories in ID number order, but I need to have both sub-categories and main categories sorted by Name order, and this is crux of the problem.
Doug
February 7, 2006 at 12:09 am
try this...
select C.* from Categories C
left join Categories S ON S.CategoryID=C.MainCatID
order by isnull(S.CategoryName , '') + C.CategoryName
February 7, 2006 at 12:40 am
Oh brilliant !!
That's it. Great stuff. I didn't think of using isnull in the order by line.
Thanks for your help. It's much appreciated.
Cheers,
Doug
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply