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 Wednesday, September 5, 2012 4:01 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
I have two tables

Table A
--------
ItemID
Date


Table B
---------
ItemID
ParentID

Records in Table A may be items with parents (or) items without parents. Some of the parents may exist as items in Table A (means both items and their parents exist as items in Table A). The goal is to order all the items in Table A by the Date column, but keeping the items and their parents together.

Example: (ItemID 2 is parent, ItemID(4,5) are standalone, ItemID (1,3) are child of ItemID 2
Table A
---------
ItemID Date
1 9/8/2012
2 8/7/2012
3 9/9/2012
4 9/10/2012
5 8/23/2012

Table B
---------
ItemID ParentID
1 2
3 2

My expected result: Order by parents date but keeping the families together.

ItemID Date
2 8/7/2012
1 9/8/2012
3 9/9/2012
5 8/23/2012
4 9/10/2012

To summairze, this result set is: order by date asc, but keeping the families together. Within the family, parent comes first, childer come next order by date asc.

I thought of applying parent's date to all childs and order by parent date. But, it doesn't order the childs properly as all the childs will have the parent date. Also, it fails in case where the parent doesn't have a date.

Is there an efficient way to accomplish this? Thanks in advance.


Post #1354934
Posted Wednesday, September 5, 2012 6:57 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: Today @ 3:25 AM
Points: 3,648, Visits: 5,328
You can try this. It might work for you but without more test data it's difficult to be sure.

DECLARE @TableA TABLE (ItemID INT, Date DATETIME)
DECLARE @TableB TABLE (ItemID INT, ParentID INT)

INSERT INTO @TableA
SELECT 1, '2012-09-08' UNION ALL SELECT 2, '2012-08-07'
UNION ALL SELECT 3, '2012-09-09' UNION ALL SELECT 4, '2012-09-10'
UNION ALL SELECT 5, '2012-08-23'

INSERT INTO @TableB
SELECT 1, 2 UNION ALL SELECT 3, 2

;WITH CTE AS (
SELECT b.ItemID, b.ParentID, a.Date
FROM @TableB b
INNER JOIN @TableA a ON b.ParentID = a.ItemID
UNION ALL
SELECT a.ItemID, NULL, a.Date
FROM @TableA a
)
SELECT a.ItemID, b.Date
FROM (
SELECT ItemID, Date, ParentID
,rn=ROW_NUMBER() OVER (PARTITION BY ItemID ORDER BY ParentID DESC)
FROM CTE
) a
INNER JOIN @TableA b ON a.ItemID = b.ItemID
WHERE rn=1
ORDER BY a.Date, ParentID, ItemID


Note that it only works for one level of parent-child hiearchy.



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 #1354975
Posted Thursday, September 6, 2012 8:49 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:15 PM
Points: 1,945, Visits: 3,008
>> I have two tables <<

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats, since it is the only one allowed in ANSI Standard SQL. Code should be in Standard SQL as much as possible and not local dialect. You did none of this.

Rows are nothing like Records [sic]. The term “parent” and “child” come from the old network data bases and have no place in RDBMS.

The design flaw you described in your vague narrative is called attribute splitting. Do you have a “Male_Personnel” and a”Female_Personnel” table? Of course not! A competent SQL programmer would have just the “Personnel” table, with a sex_code column in it.

Let me guess that this is an inventory and that you use UPC codes for identifiers. Did you know that date is a reserved word in SQL? We need a “<something>_date” according to ISO-11179; date is called an attribute property.

Your “stand-alone” items are modeled as an item family with only one member.

CREATE TABLE Inventory
(family_upc CHAR(13) NOT NULL PRIMARY KEY,
upc CHAR(13),
purchase_date DATE NOT NULL);

INSERT INTO Inventory
VALUES
(2, 1, '2012-09-08'),
(2, 2, '2012-08-07'),
(3, NULL, '2012-09-09'),
(4, NULL, '2012-09-10'),
(5, NULL, '2012-08-23');

>> My expected result: Order by parents date but keeping the families together. <<

SELECT CONVALESCE (upc, family_upc) AS item_upc, purchase_date
FROM Inventory
ORDER BY item_upc, purchase_date;

>> Is there an efficient way to accomplish this? <<

Proper schema design. You need to educate yourself on RDBMS and basic data modeling.


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 #1355372
Posted Thursday, September 6, 2012 9:35 AM


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: Today @ 5:43 PM
Points: 3,667, Visits: 8,006
CELKO (9/6/2012)

SELECT CONVALESCE (upc, family_upc) AS item_upc, purchase_date
FROM Inventory
ORDER BY item_upc, purchase_date;


CONVALESCE? I can't find these on any SQL books, just medicine.
The design is poor? it might be but it's not the actual design, just an example to ask for help.
Your solution won't work because it's not what the OP needs.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1355421
Posted Thursday, September 6, 2012 10:13 AM
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
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
Post #1355459
Posted Thursday, September 6, 2012 10:31 AM
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 - Thank you for your response and vluable suggestions. I don't see CONVALESCE function either. I am not familiar with ISO-11179 data element conventions. I just gave an example data set for the problem I am working on. The column names I chose are just to understand the scnario and it doesn't mean that I don't know any basics of data modeling, RDBMS or Date fields in SQL. I believe this forum is not just for experts and it is designed to help all levels of people.
Post #1355473
Posted Thursday, September 6, 2012 10:32 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:15 PM
Points: 1,945, Visits: 3,008
CONVALESCE? I can't find these on any SQL books, just medicine.


LOL! That was very Freudian The stitches in eye have not dissolved yet, so I depend on my spelling checker too much.

The design is poor? it might be but it's not the actual design, just an example to ask for help. Your solution won't work because it's not what the OP needs.


Most of the work (80-95%) in SQL is done in the DDL. Messy, complicated DML is almost always the result of poor DDL. This is what he needs! Giving him kludges assures he will never learn to be a good programmer.

If this was a woodworking newsgroup and someone posted "What is the best kind of rocks to pound screws into fine furniture?" are you really helping them when you say "Granite! Use big hunks of granite!" I am the guy who replies with "Your question is bad. Don't you know about screwdrivers?"



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 #1355474
Posted Thursday, September 6, 2012 10:49 AM


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: Today @ 5:43 PM
Points: 3,667, Visits: 8,006
CELKO (9/6/2012)

Most of the work (80-95%) in SQL is done in the DDL. Messy, complicated DML is almost always the result of poor DDL. This is what he needs! Giving him kludges assures he will never learn to be a good programmer.

If this was a woodworking newsgroup and someone posted "What is the best kind of rocks to pound screws into fine furniture?" are you really helping them when you say "Granite! Use big hunks of granite!" I am the guy who replies with "Your question is bad. Don't you know about screwdrivers?"


I agree, with good DDL most problems would be solved even more easily. However, most people in these forums aren't able to change that and have to work with what they have.
Don't we know about screwdrivers? yes, but we can't afford them. What should we do in that case?
Can you get the point?



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1355489
Posted Thursday, September 6, 2012 10:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 2,856, Visits: 5,124
Luis Cazares (9/6/2012)
CELKO (9/6/2012)

Most of the work (80-95%) in SQL is done in the DDL. Messy, complicated DML is almost always the result of poor DDL. This is what he needs! Giving him kludges assures he will never learn to be a good programmer.

If this was a woodworking newsgroup and someone posted "What is the best kind of rocks to pound screws into fine furniture?" are you really helping them when you say "Granite! Use big hunks of granite!" I am the guy who replies with "Your question is bad. Don't you know about screwdrivers?"


I agree, with good DDL most problems would be solved even more easily. However, most people in these forums aren't able to change that and have to work with what they have.
Don't we know about screwdrivers? yes, but we can't afford them. What should we do in that case?
Can you get the point?


"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...





_____________________________________________
"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 #1355491
Posted Thursday, September 6, 2012 11:35 AM


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: Today @ 5:43 PM
Points: 3,667, Visits: 8,006
Eugene Elutin (9/6/2012)
Luis Cazares (9/6/2012)
CELKO (9/6/2012)

Most of the work (80-95%) in SQL is done in the DDL. Messy, complicated DML is almost always the result of poor DDL. This is what he needs! Giving him kludges assures he will never learn to be a good programmer.

If this was a woodworking newsgroup and someone posted "What is the best kind of rocks to pound screws into fine furniture?" are you really helping them when you say "Granite! Use big hunks of granite!" I am the guy who replies with "Your question is bad. Don't you know about screwdrivers?"


I agree, with good DDL most problems would be solved even more easily. However, most people in these forums aren't able to change that and have to work with what they have.
Don't we know about screwdrivers? yes, but we can't afford them. What should we do in that case?
Can you get the point?


"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...



I just can't stop laughing at this.




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1355517
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse