T-SQL Challenge #1

  • Comments posted to this topic are about the item T-SQL Challenge #1

    .

  • 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]

  • I see that too, and even in IE and firefox. I have sent an email to Steve requesting to take a look at it.

    .

  • The formatting problem is corrected.

    .

  • 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;

  • I'm wondering where could in real life we can face challenge like this!

  • 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.

    .

  • 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?

  • 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

  • -- ?????

    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)

  • 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]

  • 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]

  • 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]

  • 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.

  • 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