September 5, 2012 at 4:01 pm
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.
September 5, 2012 at 6:57 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 6, 2012 at 9:35 am
CELKO (9/6/2012)
SELECT CONVALESCE (upc, family_upc) AS item_upc, purchase_dateFROM 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.
September 6, 2012 at 10:13 am
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
September 6, 2012 at 10:31 am
@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.
September 6, 2012 at 10:49 am
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?
September 6, 2012 at 10:56 am
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... :hehe:
September 6, 2012 at 11:35 am
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... :hehe:
I just can't stop laughing at this. :hehe:
September 6, 2012 at 2:55 pm
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
September 6, 2012 at 6:42 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 6, 2012 at 8:37 pm
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.
_____________
Code for TallyGenerator
September 6, 2012 at 8:41 pm
@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.
September 6, 2012 at 9:29 pm
CELKO (9/6/2012)
Have you looked up ISO-11179 ...
Where's the link to the free version?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 7, 2012 at 2:42 am
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:
September 7, 2012 at 2:56 am
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 :hehe:).
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...
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply