November 23, 2011 at 10:58 pm
Glad it's working for you. I just hope you don't have too many rows in these user tables, because that is NOT going to scale well.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 23, 2011 at 11:05 pm
No, I won't have more than 30 rows.
In fact, the newP table will be a temp/variable table and I know it's not recommended to store more than 100 records.
November 24, 2011 at 8:34 am
Sorry but it's not done yet 🙁
do this
truncate table newp
insert into NewP
select * from
(
select 5 id, 'E' name, 2 sort
Union all
select 11 id, 'K' name, 3 sort
Union all
select 2 id, 'B' name, 1 sort
Union all
select 14 id, 'N' name, 10 sort
) za
and run again ... you shall see A on position 4 but is not there 🙁
My idea is E, K, B will be grouped together because they gonna be on consecutive positions. Then I have to calculate how many of them are in each group (1st group has 3 elements: E, K, B). Having this I will be able to know with how many positions I shall push A down (with 3 positions + current position (1) = 4 as the new position of A).
November 28, 2011 at 5:30 pm
I see the problem and I think I understand where you are going with your proposed solution. It may not be possible to do this without some form of loop or recursion, but we'll try. Be back later.
For the record, I'm doing this because it's a fun problem. In my opinion, you are much better off doing all this work procedurally on the client side and then just doing a truncate/insert back to the table in the db.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 28, 2011 at 9:25 pm
Halford, I'm sorry but I couldn't make it work. You're going to need a loop, a cursor, or a recursive cte.
Anyone else who wants to try should take up the torch now.
When you test, be sure to test against multiple sequences like this:
insert into NewP
select * from
(
select 5 id, 'E' name, 2 sort
Union all
select 11 id, 'K' name, 3 sort
Union all
select 2 id, 'B' name, 1 sort
Union all
select 14 id, 'N' name, 10 sort
union all
select 13 id, 'M' name, 11 sort
union all
select 4 id, 'D' name, 14 sort
) za
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 28, 2011 at 9:29 pm
No problem. I appreciate and thank you for your time and efforts! I'll keep thinking about this and let you know about any progress.
November 29, 2011 at 9:26 am
I've been thinking about this, but hadn't come up with a workable solution until now.
WITH Remaining_Prods AS (
SELECT p.ID
,p.[Name]
,Row_Number() OVER ( ORDER BY p.Sort ) AS Seq
FROM Prods AS p
LEFT OUTER JOIN NewP as np
ON p.ID = np.ID
WHERE np.ID IS NULL
)
, Remaining_Slots AS (
SELECT p.Sort
,Row_Number() OVER ( ORDER BY p.Sort ) AS Seq
FROM Prods AS p
LEFT OUTER JOIN NewP as np
ON p.Sort = np.Sort
WHERE np.Sort IS NULL
)
SELECT rp.ID
,rp.Name
,rs.Sort
FROM Remaining_Prods AS rp
INNER JOIN Remaining_Slots AS rs
ON rp.Seq = rs.Seq
UNION ALL
SELECT np.ID
,np.Name
,np.Sort
FROM NewP AS np
ORDER BY Sort
Essentially we take the products that weren't moved and the sort positions that aren't already taken and sort them both by their original sort, because their relative order is still the same, and match them based on that.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 29, 2011 at 1:38 pm
Drew that's elegant. Simple and effective. Applause, thunderous applause.
Your insight that the relative order of the remaining prods and the remaining slots would remain the same was a keen one. I never even considered that.
Great save !! 😀
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 29, 2011 at 1:45 pm
:satisfied:
In addition to Dixie's reply I'd like to mention that this is also a programming (not just SQL) lesson. :smooooth:
Thanks!
November 29, 2011 at 2:10 pm
A good carry-away lesson from this is that set-based solutions are possible, even easy, whenever you can define the sets properly. The failed solution I attempted treated the moved rows as one set, but did not treat the slots as a set.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 29, 2011 at 2:57 pm
The Dixie Flatline (11/29/2011)
A good carry-away lesson from this is that set-based solutions are possible, even easy, whenever you can define the sets properly. The failed solution I attempted treated the moved rows as one set, but did not treat the slots as a set.
The key insight came to me from an SVG graphics tutorial I was working on last night, because it involved isolating certain sections of an object and also switching between looking at objects as objects, paths, or nodes depending on the current needs.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply