Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Order by a column keeping the families together


Order by a column keeping the families together

Author
Message
mickyT
mickyT
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1249 Visits: 3309
You could try this

;WITH cte AS (
   SELECT a.itemid, a.date
      , 0 level
      , null parentid
      , a.itemid rootid, a.date rootdate
      , CASE WHEN EXISTS(SELECT 1 FROM @tableB k WHERE a.itemid = k.parentid) THEN 1 ELSE null END hasKiddies
   FROM @tableA a
   WHERE NOT EXISTS (SELECT 1 FROM @tableB b WHERE a.itemid = b.itemid)
   UNION ALL
   SELECT b.itemid, ab.date
      , level + 1 level
      , b.parentid
      ,a.rootid, a.rootdate
      , CASE WHEN EXISTS(SELECT 1 FROM @tableB k WHERE b.itemid = k.parentid) THEN 1 ELSE null END hasKiddies
   FROM cte a
      INNER JOIN @tableB b ON a.itemid = b.parentid
      INNER JOIN @tableA ab ON ab.itemid = b.itemid
   WHERE a.hasKiddies = 1
   Wink
SELECT itemid, date
FROM cte
ORDER by rootdate, rootid, level , date



Sorry it's a bit ugly, but it should handle same dates and also multiple levels
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4231 Visits: 6431
chaseurpuli (9/6/2012)
Thanks a lot for your response Dwain. Your code is successful in keeping the families together and ordering the items by date as long as the parents have different dates. But when two or more parents have same date, it has failed in keeping the families together. It is ordering the parents with same date first and then their childs next.

In our example, if we change the Item 5's date as '2012-08-07' (same as item 2) then the results are:

ItemID Date
2    2012-08-07 00:00:00.000
5    2012-08-07 00:00:00.000
1    2012-09-08 00:00:00.000
3    2012-09-09 00:00:00.000
4    2012-09-10 00:00:00.000


You are correct! Sorry about that.

Since MickyT's seems to work, I assume you'll proceed with that.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Sergiy
Sergiy
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5812 Visits: 11390
Borrowing the DDL and data papulation scripts from dwain.c:
SELECT CA.ItemID, CA.Date 
FROM @TableA AS CA
   LEFT JOIN @TableB AS B ON CA.ItemID = B.ItemID
   LEFT JOIN @TableA AS PA ON B.ParentID = PA.ItemID
ORDER BY ISNULL(PA.Date, CA.Date), PA.Date, B.ItemID



Execution plan, as well as the code, is way shorter.
Learning1
Learning1
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 94
@CELKO - I respect your experience and expertise. I am not a great programmer and I am not writing any books. I didn't work in ANSI/ISO SQL Standards Committee either. I am just working on an issue. I posted the problem so that people of my knowledge also can understand and benefit from the solutions provided by experts. As long as people understand what my problem is I am fine. You understood what my question was, other people who replied understood what my question was and I believe any person who don't know anything about sql can also understand what my question is. If it makes you feel happy and sleep well tonight, I would be more than happy to say that I DON'T KNOW ANYTHING. If everyone writes like you and use the same language or Netiquette that you use, everyone will be authors, who will be the readers then? I just wanted to be a reader not an author.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44778 Visits: 39845
CELKO (9/6/2012)
Have you looked up ISO-11179 ...


Where's the link to the free version?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
     Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3036 Visits: 5478
CELKO (9/6/2012)
[ "Granite! Use big hunks of granite!". I would add also: polish it first!
It's cheaper than a screwdriver, and from performance point of view it can be much faster... Hehe


Polish it? Why? It will just get scratched :-)...


That is a whole point! Get it scratched! Ok, ok, it was sarcasm... Hehe

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3036 Visits: 5478


Columns are not fields. Did you learn anything? Have you looked up ISO-11179 or read a book on basic data modeling yet? Why did you fail to follow minimal Netiquette if you know the basics?



1. Columns are fields! The rest is semantic. Some people may say table columns, some other: table fields. After a bit working in industry, I will be able to recognise what they are talking about, aren't you?
2. Personally, I've learned many things starting from electronics and assembler for IBM360/380 up to playing African drums (which is much harder then programming :heheSmile.
3. I've never read any of the ISO "books" and never seen any idiot who did. I have read different books on data modelling. And I would like to note, that all ideas around data modelling are just theories. There are no much theorems there. Some ideas contradicts another, some not. It well can be matter of personal choice or, better to say real life requirements, which will dictate which one to use from time to time.
4. Etiquette? Joe, you must be joking...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Greg Snidow
Greg Snidow
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1611 Visits: 2478
CELKO (9/6/2012)
Why did you fail to follow minimal Netiquette if you know the basics?


Does anyone else see the irony in this?

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search