Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

T-SQL Challenge #1 Expand / Collapse
Author
Message
Posted Saturday, September 12, 2009 3:27 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
Comments posted to this topic are about the item T-SQL Challenge #1

.
Post #786916
Posted Saturday, September 12, 2009 5:34 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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
Posted Saturday, September 12, 2009 7:41 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
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
Posted Sunday, September 13, 2009 9:50 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
The formatting problem is corrected.

.
Post #787160
Posted Monday, September 14, 2009 3:29 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, July 14, 2014 8:27 AM
Points: 772, Visits: 240
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
Posted Monday, September 14, 2009 4:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, September 14, 2009 4:38 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
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
Posted Monday, September 14, 2009 8:00 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC 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
Posted Monday, September 14, 2009 12:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 8:17 AM
Points: 7, Visits: 134
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
Posted Monday, September 14, 2009 5:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 27, 2013 1:15 PM
Points: 1, Visits: 11
-- ?????


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 »

Add to briefcase 123»»»

Permissions Expand / Collapse