September 12, 2009 at 3:27 pm
Comments posted to this topic are about the item T-SQL Challenge #1
.
September 12, 2009 at 5:34 pm
Jacob or Steve:
There seems to be some kind of formatting problem with the code blocks over-running the text that follows them. At least for me (Chrome 2.0).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 12, 2009 at 7:41 pm
I see that too, and even in IE and firefox. I have sent an email to Steve requesting to take a look at it.
.
September 13, 2009 at 9:50 pm
The formatting problem is corrected.
.
September 14, 2009 at 3:29 am
The challenge can be easily handled in an another way like
WITH CTE1
AS
(
SELECT CODE, 1 AS ROWNUMBER, MAXROWNUMBER
FROM (
SELECT CODE, MAX(ROWNUMBER) MAXROWNUMBER
FROM ( SELECT CODE, ROW_NUMBER() OVER(PARTITION BY CODE ORDER BY CODE ASC) ROWNUMBER FROM TABLEA
UNION ALL SELECT CODE, ROW_NUMBER() OVER(PARTITION BY CODE ORDER BY CODE ASC) ROWNUMBER FROM TABLEB
UNION ALL SELECT CODE, ROW_NUMBER() OVER(PARTITION BY CODE ORDER BY CODE ASC) ROWNUMBER FROM TABLEC ) A
GROUP BY CODE )A
UNION ALL
SELECT CODE, ROWNUMBER + 1, MAXROWNUMBER
FROM CTE1
WHERE MAXROWNUMBER >= ROWNUMBER + 1
)
SELECT A.CODE, A.NAME, B.CODE, B.NAME,C.CODE, C.NAME
FROM CTE1
INNER JOIN ( SELECT *, ROW_NUMBER() OVER(PARTITION BY CODE ORDER BY CODE ASC) ROWNUMBER FROM TABLEA ) A ON A.CODE = CTE1.CODE
LEFT OUTER JOIN ( SELECT *, ROW_NUMBER() OVER(PARTITION BY CODE ORDER BY CODE ASC) ROWNUMBER FROM TABLEB ) B ON b.CODE = CTE1.CODE AND b.ROWNUMBER = CTE1.ROWNUMBER
LEFT OUTER JOIN ( SELECT *, ROW_NUMBER() OVER(PARTITION BY CODE ORDER BY CODE ASC) ROWNUMBER FROM TABLEC ) c ON c.CODE = CTE1.CODE AND c.ROWNUMBER = CTE1.ROWNUMBER
ORDER BY CTE1.CODE;
September 14, 2009 at 4:15 am
I'm wondering where could in real life we can face challenge like this!
September 14, 2009 at 4:38 am
This was a problem originally posted in one of the SQL server forums. That gives me an indication that there may be places when this type of queries are used.
Some of the challenges that we present through the 'TSQL challenges' series are taken from real-life scenarios and some are not. The goal is not only to solve the problem, but also to exercise and enhance the query writing skills and SET based thinking.
.
September 14, 2009 at 8:00 am
Whilst I enjoyed the mental exercise and learning something I didn't know about 2005, I just can't picture when I would actually do such a join in real life. Is there a logical, meaningful example that was the basis of this problem ... and if not, does anyone have any ideas of when you'd prefer results which only join multiple values in this manner?
September 14, 2009 at 12:08 pm
Hi,
I find this interesting and like the idea. I\m thinking that when you present the other solutions a brief description of why they are shown would be helpful, like this one has the fastest performance and this one solves the challenge in the fewest lines. learning the differences of simplest vs highest performing would be of great benefit to me as I continue to learn.
Thanks and looking forward to the next challenge.
Regards,
Jim
September 14, 2009 at 5:08 pm
-- ?????
DECLARE @m TABLE (ID INT IDENTITY(1,1), code INT, aname VARCHAR(10), typeT INT)
INSERT INTO @m (code , aname , typeT )
SELECT *
FROM
(
SELECT code, bname, 1 Typet
FROM @b-2
UNION ALL
SELECT code, cname, 2 Typet
FROM @C
) x
SELECTA.CODE, A.ANAME, M.ANAME, N.ANAME
FROM@mM
FULL JOIN@mN
ONM.CODE= N.CODE
ANDM.ID = (N.ID%(SELECT MIN(o.id) FROM @m O WHERE O.typet = 2 and O.code = M.code))+
((SELECT MIN(Q.id) FROM @m Q WHERE Q.typet = 1 and Q.code = M.code))
RIGHT JOIN@a A
ONA.code = M.CODE
ORA.code = N.CODE
WHERE
(M.TYPET = 1 OR M.TYPET IS NULL)
AND(N.TYPET = 2 OR N.TYPET IS NULL)
September 14, 2009 at 11:42 pm
Sharon Matyk (9/14/2009)
Whilst I enjoyed the mental exercise and learning something I didn't know about 2005, I just can't picture when I would actually do such a join in real life. Is there a logical, meaningful example that was the basis of this problem ... and if not, does anyone have any ideas of when you'd prefer results which only join multiple values in this manner?
Actually, there are a wide variety of questions on this very forum asking for practical solutions to this exact problem. In most cases these are reporting requirements.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 14, 2009 at 11:48 pm
Sharon Matyk (9/14/2009)
Whilst I enjoyed the mental exercise and learning something I didn't know about 2005, I just can't picture when I would actually do such a join in real life. Is there a logical, meaningful example that was the basis of this problem ... and if not, does anyone have any ideas of when you'd prefer results which only join multiple values in this manner?
Actually, there are a wide variety of questions on this very forum asking for practical solutions to this exact problem. In most cases these are reporting requirements.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 15, 2009 at 12:31 am
Sharon Matyk (9/14/2009)
Whilst I enjoyed the mental exercise and learning something I didn't know about 2005, I just can't picture when I would actually do such a join in real life. Is there a logical, meaningful example that was the basis of this problem ... and if not, does anyone have any ideas of when you'd prefer results which only join multiple values in this manner?
Actually, there are a wide variety of questions on this very forum asking for practical solutions to this exact problem. In most cases these are reporting requirements.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 15, 2009 at 12:40 am
I see where people are coming from, the example has no context and the reasoning behind the output is missing.
There would clearly have to be some business rules as to why you don't want to see certain combo's in certain situations, so including a basic version of those rules would help people understand why the result is formatted the way it is.
That's just the way some people work.
September 15, 2009 at 2:51 am
Agreed, it's a strange request that I've never come across.
That said, of the solutions posted, Ashish Gilhotra's is the fastest, but does not (for me at least) return the "correct" results as listed in the challenge. I could not quite get my head around the replace(count(...)-1,0,4) bit, which is using a text function to update a numeric value. If the dataset includes 10 items, should we really be replacing the answer with 14?
This is basically the same code, but tidied up so that it works.
selecta.code,a.aname, x.bname, x.cname
from@a a
left join(
selectisnull(b.code, c.code) as code, b.bname, c.cname
from(
selectb.code, (select count(*)+1 from @b-2 x where x.code = b.code and x.bname < b.bname) as rank, b.bname
from@b-2 b
)b
full join(
selectc.code, (select count(*)+1 from @C x where x.code = c.code and x.cname < c.cname) as rank, c.cname
from@C c
) c
onc.code = b.code
andc.rank = b.rank
) x
onx.code = a.code
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply