March 25, 2011 at 4:16 am
[font="Courier New"]Hi everyone, this is my first post to the forum.
------------------------------------------------
Problem
------------------------------------------------
I have this table:
customerIdrouteCoderoutePos
===========================================
278336711
278356313
278359114
278357015
278356216
278347517
278349121
278349222
278349323
278349524
278349431
278349732
278313933
278337634
278349935
278350036
278351141
278361443
278365444
278361646
278359551
278368752
278368153
278367854
278363456
278362157
278365761
278367962
278367163
278365564
278368966
278359368
278366371
278368673
278368574
278361575
I need a query or a procedure (maybe a function?) that produce this output:
custIdrouteCoderoutePos
===========================================
278336711
278349121
278356313
278349222
278359114
278349323
278357015
278349524
278356216
278349431
278347517
278349732
278351141
278313933
278361443
278337634
278365444
278349935
278361646
278350036
278359551
278365761
278368752
278367962
278368153
278367163
278367854
278365564
278363456
278368966
278362157
278359368
278366371
278368673
278368574
278361575
Any suggestions?
Thanks[/font]
March 25, 2011 at 4:23 am
I think you should clarify what business logic produces the expected output.
Without an explanation, I guess nobody will be able to help.
-- Gianluca Sartori
March 25, 2011 at 5:08 am
Gianluca, thanks for your reply.
The first table represents a list of customers belonging to several groups; every group has a code (in my table the group code is called routeCode); inside every group, customers are ordered by positions (in my table the position is called routePos).
I have to alternate customers belonging to different groups, but I have to process each customer of the first two groups, before moving to the customers belonging to the subsequent two groups, and so on.
For example (table with 14 customers divided in 3 groups):
Group 1 : 3 customers
Group 2 : 5 customers
Group 3 : 6 customers
The desired output is a list of customers ordered like the following schema:
group 1 position 1
group 2 position 1
group 1 position 2
group 2 position 2
group 1 position 3
group 2 position 3
group 3 position 1 (the group change because group 1 has only three customers)
group 2 position 4
group 3 position 2
group 2 position 5
group 3 position 3
group 3 position 4
group 3 position 5
group 3 position 6
I hope this post clarify my original request.
Thanks.
March 25, 2011 at 7:08 am
I'm sorry, I can't come up with a set-based solution for this problem.
I'll call for help.
-- Gianluca Sartori
March 25, 2011 at 7:19 am
Please try to clarify (in words, without reposting the data output) what the order logic is. Remember, we don't know your data as well as you do, so you might have to give us more to go on than someone at your business needs.
EDIT: I should add, could you please give us your table structures (DDL) as well as an INSERT for each table so we can play with the data and come up with the best solution.
Also, what do the groups represent?
March 25, 2011 at 7:25 am
Let me try to rephrase this: we have X groups with Y elements each.
We have to pull out data taking one row from group 1, one row from group 2, then one row from group 1, one row from group 2 etc...
This until one of the groups runs out of rows, then we move to the next group and continue alternating rows from groups.
-- Gianluca Sartori
March 25, 2011 at 7:52 am
For those willing to play with this, here is the OPs sample data from the first post in readily consumable format.
DECLARE @test TABLE (
customerId INTEGER,
routeCode INTEGER,
routePos INTEGER);
INSERT INTO @test
SELECT 2783367, 1, 1 UNION ALL
SELECT 2783563, 1, 3 UNION ALL
SELECT 2783591, 1, 4 UNION ALL
SELECT 2783570, 1, 5 UNION ALL
SELECT 2783562, 1, 6 UNION ALL
SELECT 2783475, 1, 7 UNION ALL
SELECT 2783491, 2, 1 UNION ALL
SELECT 2783492, 2, 2 UNION ALL
SELECT 2783493, 2, 3 UNION ALL
SELECT 2783495, 2, 4 UNION ALL
SELECT 2783494, 3, 1 UNION ALL
SELECT 2783497, 3, 2 UNION ALL
SELECT 2783139, 3, 3 UNION ALL
SELECT 2783376, 3, 4 UNION ALL
SELECT 2783499, 3, 5 UNION ALL
SELECT 2783500, 3, 6 UNION ALL
SELECT 2783511, 4, 1 UNION ALL
SELECT 2783614, 4, 3 UNION ALL
SELECT 2783654, 4, 4 UNION ALL
SELECT 2783616, 4, 6 UNION ALL
SELECT 2783595, 5, 1 UNION ALL
SELECT 2783687, 5, 2 UNION ALL
SELECT 2783681, 5, 3 UNION ALL
SELECT 2783678, 5, 4 UNION ALL
SELECT 2783634, 5, 6 UNION ALL
SELECT 2783621, 5, 7 UNION ALL
SELECT 2783657, 6, 1 UNION ALL
SELECT 2783679, 6, 2 UNION ALL
SELECT 2783671, 6, 3 UNION ALL
SELECT 2783655, 6, 4 UNION ALL
SELECT 2783689, 6, 6 UNION ALL
SELECT 2783593, 6, 8 UNION ALL
SELECT 2783663, 7, 1 UNION ALL
SELECT 2783686, 7, 3 UNION ALL
SELECT 2783685, 7, 4 UNION ALL
SELECT 2783615, 7, 5;
SELECT * FROM @test;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 25, 2011 at 8:00 am
Gianluca Sartori (3/25/2011)
Let me try to rephrase this: we have X groups with Y elements each.We have to pull out data taking one row from group 1, one row from group 2, then one row from group 1, one row from group 2 etc...
This until one of the groups runs out of rows, then we move to the next group and continue alternating rows from groups.
Okay, that's a little more clear, but what criteria are you using to judge WHICH row from Group 1 gets printed before WHICH row from Group 2?
That's what I meant by order logic.
March 25, 2011 at 8:04 am
Brandie Tarvin (3/25/2011)
Gianluca Sartori (3/25/2011)
Let me try to rephrase this: we have X groups with Y elements each.We have to pull out data taking one row from group 1, one row from group 2, then one row from group 1, one row from group 2 etc...
This until one of the groups runs out of rows, then we move to the next group and continue alternating rows from groups.
Okay, that's a little more clear, but what criteria are you using to judge WHICH row from Group 1 gets printed before WHICH row from Group 2?
That's what I meant by order logic.
If I understand correctly, looks like routePos is the field that determines the order or the rows inside groups.
-- Gianluca Sartori
March 25, 2011 at 8:13 am
From what I see....
Fill up a "group queue" with two groups, in order of the routeCode column.
Return one row from each group, in order of the routePos column.
If one of the groups runs out of rows, get the next group. (Fill up the "group queue" to have two groups, unless there aren't two groups left.)
Repeat until all have been processed.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 25, 2011 at 8:17 am
WayneS (3/25/2011)
From what I see....Fill up a "group queue" with two groups, in order of the routeCode column.
Return one row from each group, in order of the routePos column.
If one of the groups runs out of rows, get the next group. (Fill up the "group queue" to have two groups, unless there aren't two groups left.)
Repeat until all have been processed.
Yes, now translate it into T-SQL, set-based if possible. 🙂 Ugh!
-- Gianluca Sartori
March 25, 2011 at 8:39 am
What I'd really like to understand is the business requirements for producing such a sort...
Currently, all I can think of is a ...
(I just can't bring myself to say it - there has to be a set-based way.)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 25, 2011 at 8:40 am
Gianluca Sartori (3/25/2011)
WayneS (3/25/2011)
From what I see....Fill up a "group queue" with two groups, in order of the routeCode column.
Return one row from each group, in order of the routePos column.
If one of the groups runs out of rows, get the next group. (Fill up the "group queue" to have two groups, unless there aren't two groups left.)
Repeat until all have been processed.
Yes, now translate it into T-SQL, set-based if possible. 🙂 Ugh!
Sorry, guys. I know it seems obvious, but I have been bitten by the assumption bug more than once when the BU turned around and said "That's not what I meant. Why didn't you ask?"
Hence the reason I always ask.
March 25, 2011 at 8:43 am
It's a relatively simple mathematical pattern, but more rules are needed to have a complete solution.
For example, what would you do with a group that has more entries than can alternate with another group? If, for example, you have a set with 1 record in group 1 and 4 records in group 2, and that's all you have, you would go 1, 2, 2, 2, 2, based on the rules thus far.
The basic math would be to break it down by odd and even group numbers, and then assign sequenced "sub groups" based on quantities in alternating odd-even sets. For example, if you have 3 of 1 and 6 of 2, you need 3 of 3 to make a sub group (pattern would then be 1,2,1,2,1,2,3,2,3,2,3,2). But what happens if you don't have enough of 3 to fill that out? Then you have to include 4 as if it were odd. And so on.
So, it's basically select how many you have of your lowest group and how many of the next group, and whichever is bigger, you select top X of the other plus an ordered set of subsequent records where they aren't in the bigger group.
This certainly could be programmed as a set-based computation, but it doesn't lend itself well to that because it assumes that rows inherently have a specific order, and that's a violation of set-based relational definition.
So, I'd simplify the whole thing, and just use procedural code for it. A While loop would probably be easiest. Might look something like this:
IF OBJECT_ID('#T') IS NOT NULL
DROP TABLE #T ;
IF OBJECT_ID('#T2') IS NOT NULL
DROP TABLE #T2 ;
SELECT 2783367 AS customerId,
1 AS routeCode,
1 AS routePos
INTO #T ;
INSERT INTO #T
(customerId, routeCode, routePos)
VALUES (2783563, 1, 3),
(2783591, 1, 4),
(2783570, 1, 5),
(2783562, 1, 6),
(2783475, 1, 7),
(2783491, 2, 1),
(2783492, 2, 2),
(2783493, 2, 3),
(2783495, 2, 4),
(2783494, 3, 1),
(2783497, 3, 2),
(2783139, 3, 3),
(2783376, 3, 4),
(2783499, 3, 5),
(2783500, 3, 6),
(2783511, 4, 1),
(2783614, 4, 3),
(2783654, 4, 4),
(2783616, 4, 6),
(2783595, 5, 1),
(2783687, 5, 2),
(2783681, 5, 3),
(2783678, 5, 4),
(2783634, 5, 6),
(2783621, 5, 7),
(2783657, 6, 1),
(2783679, 6, 2),
(2783671, 6, 3),
(2783655, 6, 4),
(2783689, 6, 6),
(2783593, 6, 8),
(2783663, 7, 1),
(2783686, 7, 3),
(2783685, 7, 4),
(2783615, 7, 5) ;
CREATE TABLE #T2
(ID INT PRIMARY KEY,
customerID INT,
routeCode INT,
routePos INT) ;
DECLARE @ID INT = 1 ;
INSERT INTO #T2
(ID,
customerID,
routeCode,
routePos
)
SELECT TOP 1
@ID,
customerID,
routeCode,
routePos
FROM #T
ORDER BY routeCode,
routePos ;
WHILE @@ROWCOUNT > 0
BEGIN
SET @ID = @ID + 1 ;
INSERT INTO #T2
(ID,
customerID,
routeCode,
routePos
)
SELECT TOP 1
@ID,
customerID,
routeCode,
routePos
FROM #T
WHERE #T.customerId NOT IN (SELECT customerID
FROM #T2)
AND #T.routeCode != (SELECT routeCode
FROM #T2
WHERE ID = @ID - 1)
ORDER BY routeCode,
routePos ;
END ;
SELECT *
FROM #T2 ;
Note that this script will only work in SQL 2008 or later because of the Table Value Construct. You'll need to modify the Insert Values statement to work in 2005 or earlier. The rest of it isn't version-specific.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 25, 2011 at 8:48 am
GSquared (3/25/2011)
... but more rules are needed to have a complete solution...
Gus, you have a talent for seeing the same concerns as I do, but articulating them much better. Thank you for that post.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply