SQL Clone
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
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2860 Visits: 3318
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
)
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
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18007 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
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25824 Visits: 12469
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 Veteran
SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)

Group: General Forum Members
Points: 220 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 Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217132 Visits: 41991
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Eugene Elutin
Eugene Elutin
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12590 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
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12590 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
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4381 Visits: 2494
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