tag:blogger.com,1999:blog-4282222233168200124.post6529862854455079104..comments2023-11-02T04:19:29.341-04:00Comments on Zen of SQL: Updates with CTEPlamen Ratchevhttp://www.blogger.com/profile/02027705815827955614noreply@blogger.comBlogger7125tag:blogger.com,1999:blog-4282222233168200124.post-10677724242105577032009-07-08T10:01:49.446-04:002009-07-08T10:01:49.446-04:00Hi Dardan,
This solution does not return all matc...Hi Dardan,<br /><br />This solution does not return all matches and the return matches are incorrect. Here is sample data to try it:<br /><br />CREATE TABLE Boys (<br /> boyname VARCHAR(35) NOT NULL PRIMARY KEY,<br /> boyheight DECIMAL(2, 1));<br /> <br />CREATE TABLE Girls (<br /> girlname VARCHAR(35) NOT NULL PRIMARY KEY,<br /> girlheight DECIMAL(2, 1));<br /><br />INSERT INTO Boys (boyname, boyheight) VALUES('Joe', 5.5);<br />INSERT INTO Boys (boyname, boyheight) VALUES('Jim', 5.2);<br />INSERT INTO Boys (boyname, boyheight) VALUES('Jeff', 6.1);<br />INSERT INTO Boys (boyname, boyheight) VALUES('John', 5.8);<br />INSERT INTO Boys (boyname, boyheight) VALUES('Greg', 5.4);<br />INSERT INTO Boys (boyname, boyheight) VALUES('Tito', 5.4);<br />INSERT INTO Boys (boyname, boyheight) VALUES('Gary', 5.5);<br />INSERT INTO Boys (boyname, boyheight) VALUES('Koko', 5.7);<br />INSERT INTO Boys (boyname, boyheight) VALUES('Rando', 6.0);<br />INSERT INTO Boys (boyname, boyheight) VALUES('Gordo', 6.4);<br />INSERT INTO Girls (girlname, girlheight) VALUES('Jane', 5.3);<br />INSERT INTO Girls (girlname, girlheight) VALUES('Jody', 5.4);<br />INSERT INTO Girls (girlname, girlheight) VALUES('Mary', 5.4);<br />INSERT INTO Girls (girlname, girlheight) VALUES('Jill', 5.4);<br />INSERT INTO Girls (girlname, girlheight) VALUES('Julie', 5.6);<br />INSERT INTO Girls (girlname, girlheight) VALUES('Sarah', 6.6);<br />INSERT INTO Girls (girlname, girlheight) VALUES('Jina', 5.6);<br />INSERT INTO Girls (girlname, girlheight) VALUES('Rite', 5.9);<br />INSERT INTO Girls (girlname, girlheight) VALUES('Lola', 6.3);<br /><br />With this sample data the correct matches are:<br /><br />girlname girlheight boyname boyheight<br />--------- ----------- -------- ---------<br />Jane 5.3 Greg 5.4<br />Jill 5.4 Gary 5.5<br />Jody 5.4 Joe 5.5<br />Mary 5.4 Koko 5.7<br />Jina 5.6 John 5.8<br />Julie 5.6 Rando 6.0<br />Rite 5.9 Jeff 6.1<br />Lola 6.3 Gordo 6.4<br />Sarah 6.6 NULL NULLPlamen Ratchevhttps://www.blogger.com/profile/02027705815827955614noreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-14321241525041699052009-07-08T07:00:25.638-04:002009-07-08T07:00:25.638-04:00What about this one...
WITH Matches AS
(
SEL...What about this one...<br /><br />WITH Matches AS <br />(<br /> SELECT g.[Girl_Name]<br /> ,g.[Girl_Height]<br /> ,b.[Boy_Name]<br /> ,b.[Boy_Height]<br /> ,DENSE_RANK() OVER(ORDER BY g.[Girl_Height] ,g.[Girl_Name]) gdr<br /> ,DENSE_RANK() OVER(ORDER BY b.[Boy_Height] ,b.[Boy_Name]) bdr<br /> FROM Girls g<br /> LEFT JOIN Boys b<br /> ON b.[Boy_Height] > g.[Girl_Height]<br />)<br />SELECT [Girl_Name]<br /> ,[Girl_Height]<br /> ,[Boy_Name]<br /> ,[Boy_Height]<br />FROM Matches<br />WHERE gdr = bdrUnknownhttps://www.blogger.com/profile/00833830875510109013noreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-41389692424168184052009-07-07T11:31:11.084-04:002009-07-07T11:31:11.084-04:00Hi Dardan,
Your solution will not produce the des...Hi Dardan,<br /><br />Your solution will not produce the desired results because it returns multiple matches and does not eliminate boys that are already matched.Plamen Ratchevhttps://www.blogger.com/profile/02027705815827955614noreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-51097417245826989982009-07-07T06:17:17.065-04:002009-07-07T06:17:17.065-04:00Hello!
Maybe I'm posting too late.
Lately i&#...Hello!<br /><br />Maybe I'm posting too late.<br />Lately i've come across your blog and I like your posts allot!<br /><br />Think I have another solution for Girls/Boys. What do you think?<br /><br />WITH Matches AS <br />(<br /> SELECT g.[Girl_Name]<br /> ,g.[Girl_Height]<br /> ,b.[Boy_Name]<br /> ,b.[Boy_Height]<br /> ,MIN([Boy_Height]) OVER(PARTITION BY g.[Girl_Name]) [Min_Boy_Height]<br /> FROM Girls g<br /> LEFT JOIN Boys b<br /> ON b.[Boy_Height] > g.[Girl_Height]<br />)<br />SELECT m.Girl_Name<br /> ,m.Girl_Height<br /> ,m.Boy_Name<br /> ,m.Boy_Height<br />FROM Matches m<br />WHERE m.[Boy_Height] = m.[Min_Boy_Height]Unknownhttps://www.blogger.com/profile/00833830875510109013noreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-90289676751135671922009-03-25T07:14:00.000-04:002009-03-25T07:14:00.000-04:00Hi Plamen,That was totally cool - took me some tim...Hi Plamen,<BR/><BR/>That was totally cool - took me some time to wrap my head around the logic.<BR/><BR/>I removed the NULL check since I don't need unmatched entries, and that brought down exec time for a 2K-row test run from 00:02:25 to 00:00:18 --<BR/><BR/>WITH Ranked<BR/>AS (<BR/> SELECT boyname, boyheight, girlname, girlheight,<BR/> DENSE_RANK() OVER(ORDER BY girlheight, girlname) AS girl_rk,<BR/> DENSE_RANK() OVER(ORDER BY boyheight, boyname) AS boy_rk,<BR/> ROW_NUMBER() OVER(PARTITION BY girlname ORDER BY boyheight, boyname) AS boy_girl_rk<BR/> FROM Girls AS G<BR/> JOIN Boys AS B<BR/> ON G.girlheight < B.boyheight),<BR/>Match<BR/>AS ( <BR/> SELECT boyname, boyheight, girlname, girlheight, girl_rk, boy_rk, boy_girl_rk<BR/> FROM Ranked<BR/> WHERE girl_rk = 1<BR/> AND boy_girl_rk = 1<BR/> UNION ALL<BR/> SELECT R.boyname, R.boyheight, R.girlname, R.girlheight, R.girl_rk, R.boy_rk, R.boy_girl_rk<BR/> FROM Ranked AS R<BR/> JOIN Match AS M<BR/> ON R.girl_rk = M.girl_rk + 1<BR/> AND R.boy_rk > M.boy_rk <BR/> AND R.boy_girl_rk = <BR/> (SELECT T.boy_girl_rk<BR/> FROM <BR/> (<BR/> SELECT R2.boy_girl_rk,<BR/> ROW_NUMBER() OVER(ORDER BY R2.boy_girl_rk) AS rk<BR/> FROM Ranked AS R2<BR/> WHERE R2.girl_rk = R.girl_rk<BR/> AND R2.boy_rk > M.boy_rk<BR/> ) AS T<BR/> WHERE rk = 1<BR/> ) <BR/> )<BR/>SELECT girlname, girlheight, boyname, boyheight<BR/>FROM Match<BR/>OPTION (MAXRECURSION 0);<BR/><BR/>I also tried rewriting with APPLY/ another CTE, and they each yielded 00:00:09 --<BR/><BR/>APPLY -><BR/><BR/>WITH Ranked<BR/>AS (<BR/> SELECT boyname, boyheight, girlname, girlheight,<BR/> DENSE_RANK() OVER(ORDER BY girlheight, girlname) AS girl_rk,<BR/> DENSE_RANK() OVER(ORDER BY boyheight, boyname) AS boy_rk,<BR/> ROW_NUMBER() OVER(PARTITION BY girlname ORDER BY boyheight, boyname) AS boy_girl_rk<BR/> FROM Girls AS G<BR/> JOIN Boys AS B<BR/> ON G.girlheight < B.boyheight),<BR/>Match<BR/>AS ( <BR/> SELECT boyname, boyheight, girlname, girlheight, girl_rk, boy_rk, boy_girl_rk<BR/> FROM Ranked<BR/> WHERE girl_rk = 1<BR/> AND boy_girl_rk = 1<BR/> UNION ALL<BR/> SELECT R.boyname, R.boyheight, R.girlname, R.girlheight, R.girl_rk, R.boy_rk, R.boy_girl_rk<BR/> FROM Ranked AS R<BR/> JOIN Match AS M<BR/> ON R.girl_rk = M.girl_rk + 1<BR/> AND R.boy_rk > M.boy_rk <BR/> CROSS APPLY <BR/> (<BR/> SELECT R2.boy_girl_rk,<BR/> ROW_NUMBER() OVER(ORDER BY R2.boy_girl_rk) AS rk<BR/> FROM Ranked AS R2<BR/> WHERE R2.girl_rk = R.girl_rk<BR/> AND R2.boy_rk > M.boy_rk<BR/> ) AS T<BR/> where T.boy_girl_rk = R.boy_girl_rk<BR/> and T.rk = 1 <BR/> )<BR/>SELECT girlname, girlheight, boyname, boyheight<BR/>FROM Match<BR/>OPTION (MAXRECURSION 0);<BR/><BR/>Another CTE -><BR/><BR/>WITH Ranked<BR/>AS (<BR/> SELECT boyname, boyheight, girlname, girlheight,<BR/> DENSE_RANK() OVER(ORDER BY girlheight, girlname) AS girl_rk,<BR/> DENSE_RANK() OVER(ORDER BY boyheight, boyname) AS boy_rk,<BR/> ROW_NUMBER() OVER(PARTITION BY girlname ORDER BY boyheight, boyname) AS boy_girl_rk<BR/> FROM Girls AS G<BR/> JOIN Boys AS B<BR/> ON G.girlheight < B.boyheight),<BR/>MinRanked<BR/>AS (<BR/> SELECT MIN(R4.boy_girl_rk) as boy_girl_rk, R4.girl_rk, R5.boy_rk<BR/> FROM Ranked R4<BR/> JOIN Ranked R5<BR/> ON R4.girl_rk = R5.girl_rk + 1<BR/> AND R4.boy_rk > R5.boy_rk<BR/> GROUP BY R4.girl_rk, R5.boy_rk<BR/> ),<BR/>Match<BR/>AS ( <BR/> SELECT boyname, boyheight, girlname, girlheight, girl_rk, boy_rk, boy_girl_rk<BR/> FROM Ranked<BR/> WHERE girl_rk = 1<BR/> AND boy_girl_rk = 1<BR/> UNION ALL<BR/> SELECT R.boyname, R.boyheight, R.girlname, R.girlheight, R.girl_rk, R.boy_rk, R.boy_girl_rk<BR/> FROM Ranked AS R<BR/> JOIN Match AS M<BR/> ON R.girl_rk = M.girl_rk + 1<BR/> AND R.boy_rk > M.boy_rk <BR/> JOIN MinRanked AS R3<BR/> ON R3.girl_rk = R.girl_rk<BR/> AND R3.boy_rk = M.boy_rk<BR/> AND R3.boy_girl_rk = R.boy_girl_rk<BR/> )<BR/>SELECT girlname, girlheight, boyname, boyheight<BR/>FROM Match<BR/>OPTION (MAXRECURSION 0);<BR/><BR/>You're right though- cursors are quicker still and I'll probably stick to them for the stored procedure I was working on, but this logic was wonderful to read nevertheless. Thanks!Ramhttps://www.blogger.com/profile/09757925157716852610noreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-5312379001326852872009-03-23T18:12:00.000-04:002009-03-23T18:12:00.000-04:00Hi Ram,This is a problem that is currently best so...Hi Ram,<BR/>This is a problem that is currently best solved using a cursor. You could use a recursive CTE but performance will not be good on a large result set. Here is example of how it can be solved using a recursive CTE.<BR/><BR/>WITH Ranked<BR/>AS (<BR/>SELECT boyname, boyheight, girlname, girlheight,<BR/> DENSE_RANK() OVER(ORDER BY girlheight, girlname) AS girl_rk,<BR/> DENSE_RANK() OVER(ORDER BY boyheight, boyname) AS boy_rk,<BR/> ROW_NUMBER() OVER(PARTITION BY girlname ORDER BY boyheight, <BR/> boyname) AS boy_girl_rk<BR/>FROM Girls AS G<BR/>LEFT JOIN Boys AS B<BR/> ON G.girlheight < B.boyheight),<BR/>Match<BR/>AS ( <BR/>SELECT boyname, boyheight, <BR/> girlname, girlheight, <BR/> girl_rk, boy_rk, boy_girl_rk<BR/>FROM Ranked<BR/>WHERE girl_rk = 1<BR/> AND boy_girl_rk = 1<BR/>UNION ALL<BR/>SELECT R.boyname, R.boyheight, <BR/> R.girlname, R.girlheight, <BR/> R.girl_rk, R.boy_rk, R.boy_girl_rk<BR/>FROM Ranked AS R<BR/>JOIN Match AS M<BR/> ON R.girl_rk = M.girl_rk + 1<BR/> AND (R.boyname IS NULL<BR/> OR (R.boy_rk > M.boy_rk<BR/> AND R.boy_girl_rk = <BR/> (SELECT T.boy_girl_rk<BR/> FROM (SELECT R2.boy_girl_rk,<BR/> ROW_NUMBER() OVER(ORDER BY R2.boy_girl_rk) AS rk<BR/> FROM Ranked AS R2<BR/> WHERE R2.girl_rk = R.girl_rk<BR/> AND R2.boy_rk > M.boy_rk) AS T<BR/> WHERE rk = 1))))<BR/>SELECT girlname, girlheight, boyname, boyheight<BR/>FROM Match<BR/>OPTION (MAXRECURSION 0);Plamen Ratchevhttps://www.blogger.com/profile/02027705815827955614noreply@blogger.comtag:blogger.com,1999:blog-4282222233168200124.post-70999275473801372182009-03-20T10:36:00.000-04:002009-03-20T10:36:00.000-04:00hi,your algorithms are really cool. can you think ...hi,<BR/><BR/>your algorithms are really cool. can you think of a simple way to solve this problem (i'm using aliases since i can't give the business context)-<BR/><BR/>i have a table (boyname, boyheight) and another table (girlname, girlheight). the mandate is that i need to start matching from the shortest girl to the tallest girl, and for each girl taken in this order, the match is the shortest available boy who is taller than her.<BR/><BR/>the simplest algo would be -<BR/>1. girls {left join} boys with constraint as girl shorter than boy<BR/>2. take [girl, height] into cursor in ascending order of height<BR/>3. for each cursor, get the boy with min height from the joined table - delete this boy from all other girls and delete the cursor girl from all other boys<BR/>4. go to next cursor and repeat<BR/><BR/>i've done this using cursors, while loops and even semi-batched whiles, but i'm not convinced that's the best that can be done. surely there must be *some* way of doing this without using loops? i tried fitting this around recursive CTE's, but I'm obviously not as used to them yet.Ramhttps://www.blogger.com/profile/09757925157716852610noreply@blogger.com