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: Tuesday, July 28, 2015 5:36 PM
Points: 3,964, Visits: 6,324
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Post #1354975
Posted Thursday, September 6, 2012 9:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:14 AM
Points: 5,061, Visits: 11,627
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.
General Disclaimer:
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:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:14 AM
Points: 5,061, Visits: 11,627
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.
General Disclaimer:
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: Tuesday, July 28, 2015 8:11 AM
Points: 2,926, Visits: 5,408
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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:14 AM
Points: 5,061, Visits: 11,627
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.
General Disclaimer:
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
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: Monday, July 27, 2015 7:45 PM
Points: 1,200, Visits: 3,240
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: Tuesday, July 28, 2015 5:36 PM
Points: 3,964, Visits: 6,324
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
Post #1355677
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse