vinu512 (1/18/2013)
Hi Dwain,I don't think your query would work if I change the data a little.
Try your query with this new data:
Insert Into ES_SubModuleMaster
Select 1,1,'Academic',2013-01-01 00:00:00.0002013-01-04 00:00:00.000Start0
Union ALL
Select 2,1,'Administration',2013-01-04 00:00:00.0002013-01-07 00:00:00.000Start0
Union ALL
Select 3,1,'Information',2013-01-07 00:00:00.0002013-01-08 00:00:00.000Start0
Union ALL
Select 4,1,'Personal Information',2013-01-10 00:00:00.0002013-01-12 00:00:00.000Start0
Union ALL
Select 5,1,'School Setup',2013-01-13 00:00:00.0002013-01-15 00:00:00.000Start0
Union ALL
Select 6,1,'Sign Out',2013-01-16 00:00:00.0002013-01-17 00:00:00.000Start0
Union ALL
Select 7,1,'Welcome Page',2013-01-18 00:00:00.0002013-01-21 00:00:00.000Start0
Union ALL
Select 8,1,'Content Management',2013-01-01 00:00:00.0002013-01-03 00:00:00.000Start1
Union ALL
Select 9,1,'Content',2013-01-01 00:00:00.0002013-01-03 00:00:00.000Start8
Union ALL
Select 11,1,'Shared File List',2013-01-08 00:00:00.0002013-01-14 00:00:00.000Start8
Union ALL
Select 12,1,'Shared File List For Faculty',2013-01-01 00:00:00.0002013-01-07 00:00:00.000Start8
Union ALL
Select 13,1,'Alumni List',2013-01-01 00:00:00.0002013-01-05 00:00:00.000Start2
Union ALL
Select 15,1,'Content Management For Parent',2013-01-01 00:00:00.0002013-01-13 00:00:00.000Start1
Union ALL
Select 16,1,'InContent',2013-01-15 00:00:00.0002013-01-17 00:00:00.000Start9
Any other options??....I'm still stuck with the Ordering :crying:
It probably won't work if you added levels. See how I constructed SM1 and SM2? You'd need to do the same down to whatever level you think you've got and then use them in the ORDER BY.
Didn't say it was a complete solution. Just all I had time for and there to get you thinking about other ways to get your ordering right. A fully generalized solution to any level might require dynamic SQL.
Edit: Oh yes. And if you'd like me to look again, please post expected results as I'd rather not guess. 🙂
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St