T-SQL Challenge #1

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Jacob Sebastian » T-SQL Challenge #1

21 posts, Page 1 of 3

1 2 3 »»»

T-SQL Challenge #1

Rate Topic

Display Mode

Topic Options

Author

Message

jacob sebastian

Posted Saturday, September 12, 2009 3:27 PM

SSC-Addicted

Group: General Forum Members
Last Login: Saturday, May 11, 2013 8:17 AM
Points: 460, Visits: 2,521

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

.

Post #786916

RBarryYoung

Posted Saturday, September 12, 2009 5:34 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374

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

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #786927

jacob sebastian

Posted Saturday, September 12, 2009 7:41 PM

SSC-Addicted

Group: General Forum Members
Last Login: Saturday, May 11, 2013 8:17 AM
Points: 460, Visits: 2,521

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

.

Post #786937

jacob sebastian

Posted Sunday, September 13, 2009 9:50 PM

SSC-Addicted

Group: General Forum Members
Last Login: Saturday, May 11, 2013 8:17 AM
Points: 460, Visits: 2,521

The formatting problem is corrected.

.

Post #787160

sample1

Posted Monday, September 14, 2009 3:29 AM

Right there with Babe

Group: General Forum Members
Last Login: Friday, March 08, 2013 10:27 AM
Points: 771, Visits: 211

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;

Post #787266

alok.dixit

Posted Monday, September 14, 2009 4:15 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, October 28, 2009 5:37 AM
Points: 2, Visits: 5

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

Post #787289

jacob sebastian

Posted Monday, September 14, 2009 4:38 AM

SSC-Addicted

Group: General Forum Members
Last Login: Saturday, May 11, 2013 8:17 AM
Points: 460, Visits: 2,521

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.

.

Post #787309

Sharon Matyk

Posted Monday, September 14, 2009 8:00 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 26, 2010 10:00 AM
Points: 92, Visits: 47

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?

Post #787478

Jim-872017

Posted Monday, September 14, 2009 12:08 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, April 18, 2013 3:20 PM
Points: 6, Visits: 108

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

Post #787656

frere01

Posted Monday, September 14, 2009 5:08 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, March 22, 2012 8:46 AM
Points: 1, Visits: 10

-- ?????

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
UNION ALL
SELECT code, cname, 2 Typet
FROM @c
) x

SELECT A.CODE, A.ANAME, M.ANAME, N.ANAME
FROM @m M
FULL JOIN @m N
ON M.CODE = N.CODE
AND M.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
ON A.code = M.CODE
OR A.code = N.CODE
WHERE
(M.TYPET = 1 OR M.TYPET IS NULL)
AND (N.TYPET = 2 OR N.TYPET IS NULL)

Post #787822

« Prev Topic | Next Topic »

21 posts, Page 1 of 3

1 2 3 »»»

Permissions