Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Order by a column keeping the families together Expand / Collapse
Author
Message
Posted Thursday, September 6, 2012 2:55 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 12:17 PM
Points: 1,095, Visits: 3,182
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
Post #1355624
Posted Thursday, September 6, 2012 6:42 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:53 PM
Points: 3,438, Visits: 5,390
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!
Post #1355677
Posted Thursday, September 6, 2012 7:35 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:12 PM
Points: 1,945, Visits: 3,180
[ "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...


Polish it? Why? It will just get scratched . Nah! Smash away! LOL! Back in my youth, I had to work on projects like that. One of my favorites was State automotive tag system that was always $5 off. Nobody knew why, so they had been adding $5 to a total for almost a decade. When we moved the system to a DEC minicomputer, the guy in charge of the project could not adjust to the idea of getting a correct total without the "$5 problem" any more.

This gave me "Higgins Law of Programming Errors", whcih is that if a problem has been around so long it has a name, you are in more trouble than you know.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1355690
Posted Thursday, September 6, 2012 7:40 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:12 PM
Points: 1,945, Visits: 3,180
.. it doesn't mean that I don't know any basics of data modeling, RDBMS or Date fields [sic] in SQL. I believe this forum is not just for experts and it is designed to help all levels of people.


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?



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1355691
Posted Thursday, September 6, 2012 8:37 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
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.
Post #1355700
Posted Thursday, September 6, 2012 8:41 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 23, 2014 9:48 AM
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.
Post #1355701
Posted Thursday, September 6, 2012 9:29 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1355708
Posted Friday, September 7, 2012 2:42 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 10:09 AM
Points: 2,876, Visits: 5,201
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...


Polish it? Why? It will just get scratched ...


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


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

How to post your question to get the best and quick help
Post #1355822
Posted Friday, September 7, 2012 2:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 10:09 AM
Points: 2,876, Visits: 5,201


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 ).
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1355834
Posted Friday, September 7, 2012 6:14 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 1,564, Visits: 2,397
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.
Post #1355936
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse