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 8:37 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, July 27, 2015 7:05 AM
Points: 4,645, Visits: 8,527
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: Yesterday @ 5:26 PM
Points: 38,006, Visits: 34,922
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: Tuesday, July 28, 2015 8:11 AM
Points: 2,926, Visits: 5,408
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: Tuesday, July 28, 2015 8:11 AM
Points: 2,926, Visits: 5,408


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: Tuesday, July 7, 2015 2:25 PM
Points: 1,577, Visits: 2,451
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
Posted Friday, September 7, 2012 7:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:19 AM
Points: 7,329, Visits: 15,457
CELKO (9/6/2012)
.. 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?



Microsoft use column and field interchangeably in SQL Server error messages:
SELECT message_id, text 
FROM sys.messages
WHERE message_id IN (4158,4406,4866,8618,14380)
AND language_id = 1033



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1355964
Posted Friday, September 7, 2012 8:33 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 3:45 PM
Points: 21,913, Visits: 34,671
ChrisM@Work (9/7/2012)
CELKO (9/6/2012)
.. 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?



Microsoft use column and field interchangeably in SQL Server error messages:
SELECT message_id, text 
FROM sys.messages
WHERE message_id IN (4158,4406,4866,8618,14380)
AND language_id = 1033



I guess that in the ivory tower in which he lives with his pet mouse they are not interchangeable. Each as a specific meaning and they are not the same.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1356012
Posted Friday, September 7, 2012 3:55 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:07 PM
Points: 2,948, Visits: 4,504

1. Columns are fields!


Technically they are not.

But in practice, many people refer to SQL columns as "fields", so you just have to accept that and move on.


SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1356276
Posted Monday, September 10, 2012 3:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 28, 2015 8:11 AM
Points: 2,926, Visits: 5,408
ScottPletcher (9/7/2012)

1. Columns are fields!


Technically they are not.

But in practice, many people refer to SQL columns as "fields", so you just have to accept that and move on.


What do you mean by "technically"? Technically, all things in a computer are just electricity impulses (charge or no-charge - 1's and 0's).
As mentioned in above posts, even Microsoft sometimes uses both words for the same thing.



_____________________________________________
"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 #1356618
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse