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 2 of 3

««1 2 3 »»

T-SQL Challenge #1

Rate Topic

Display Mode

Topic Options

Author

Message

RBarryYoung

Posted Monday, September 14, 2009 11:42 PM

SSCrazy Eights

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

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.

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

Post #787901

RBarryYoung

Posted Monday, September 14, 2009 11:48 PM

SSCrazy Eights

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

Sharon Matyk (9/14/2009)

Post #787905

RBarryYoung

Posted Tuesday, September 15, 2009 12:31 AM

SSCrazy Eights

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

Sharon Matyk (9/14/2009)

Post #787921

David in .AU

Posted Tuesday, September 15, 2009 12:40 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, March 17, 2013 5:34 PM
Points: 521, Visits: 543

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.

Post #787925

paul_ramster

Posted Tuesday, September 15, 2009 2:51 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, April 26, 2013 3:18 AM
Points: 276, Visits: 524

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.

select		a.code,	a.aname, x.bname, x.cname
from		@a a
left join	(
			select		isnull(b.code, c.code) as code, b.bname, c.cname
			from		(
						select		b.code, (select count(*)+1 from @b x where x.code = b.code and x.bname < b.bname) as rank, b.bname
						from		@b b
						)b
			full join	(
						select		c.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
				on		c.code = b.code
				and		c.rank = b.rank
			) x
	on		x.code = a.code

Post #787993

Ashish Gilhotra

Posted Tuesday, September 15, 2009 3:41 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 10:05 PM
Points: 50, Visits: 41

Ohk Thanks for acknowledge this,,, i write this to solve in SQL 2000 and only for that same data i generate row numbers in this so i write this if you some solution which works on all the data then what i write..

-- Code for Challenge #1
SELECT T1.code, T1.aname, D.bname, D.cname
FROM TableA T1
LEFT JOIN
(
SELECT COALESCE(D2.code, D3.code) AS code
,D2.bname
,D3.cname
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY T2.code ORDER BY T2.bname) AS RowID
,T2.*
FROM TableB T2
) D2
FULL JOIN
(
SELECT ROW_NUMBER() OVER (PARTITION BY T3.code ORDER BY T3.cname) AS RowID
,T3.*
FROM TableC T3
) D3
ON D2.code = D3.code
AND D2.RowId = D3.RowId
) D
ON T1.code = D.code

Post #788033

Jeff Moden

Posted Tuesday, September 15, 2009 6:09 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:33 PM
Points: 32,902, Visits: 26,783

Sharon Matyk (9/14/2009)

Heh... I don't know about now, but I went through that with customer placement with AT&T years ago... even though a 3.5" diskette could hold thousands of new customer records, they only allowed us to put 100 on a disk and they had to all be in the same "region". Even when they shifted to FTP uploads, they still kept the paradigm of only 100 customers in a batch.

The reason why they did this is they would have a keypunch person verify each phone number manually then prep a work order for a tech. They used 100 as the breaking point for how many a tech should be able to handle in a day.

I think they're a little more efficient now... well... maybe. Tongue

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #788083

AlexT-359331

Posted Tuesday, September 15, 2009 9:50 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 05, 2013 8:51 AM
Points: 138, Visits: 574

WITH dd(code, aname, bname, ROWNUMBER) as
(
select a.code, a.aname, b.bname, ROW_NUMBER() OVER(PARTITION BY a.code order BY a.code )
from @a as a
left outer join @b as b on a.code=b.code
),
ddd(code, cname, ROWNUMBER) as
(
select c.code, c.cname, ROW_NUMBER() OVER(PARTITION BY code order BY code )
from @c as c
)
select COALESCE(d.code, d1.code) as code, COALESCE(aname,(select top 1 a.aname from @a a where a.code=code)) as aname, d.bname, d1.cname
from dd as d
full join ddd as d1 on d.code=d1.code and d.ROWNUMBER=d1.ROWNUMBER;

with d(name,code,ROW)
as(
SELECT bname,code,ROW_NUMBER() OVER(PARTITION BY code order by code) AS row
FROM @b b
),
dd(name,code,ROW) as
(SELECT cname,code,ROW_NUMBER() OVER(PARTITION BY code order by code) AS row
FROM @c
)
SELECT a.* , b.name1, b.name2
FROM @a a
LEFT JOIN (
select d.code as code1, d.name as name1, dd.code as code2, dd.name as name2
from d
FULL JOIN dd ON d.code = dd.code AND d.row = dd.row ) b
ON a.code IN (b.code1, b.code2)

Post #788297

RBarryYoung

Posted Tuesday, September 15, 2009 1:27 PM

SSCrazy Eights

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

Ouch! I just realized that I triple-posted the other day when I was having a lot of trouble with the site... w00t

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

Post #788493

Vamsee Krishna A

Posted Wednesday, May 18, 2011 11:03 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 18, 2011 11:20 PM
Points: 1, Visits: 3

First we need to full join the tables @b and @c based on the code and the position of the bname and cname. Then later make right join with @a table.

select a1.code,a1.aname,b1.bname,c1.cname
from @c c1
full join @b b1 
	on c1.code=b1.code and 
(select count(c2.code) from @c c2 where c1.cname>=c2.cname  and c1.code=c2.code)
	=
(select count(b2.code) from @b b2 where b1.bname>=b2.bname and b1.code=b2.code)
right join @a a1 on isnull(b1.code,c1.code)= a1.code

Post #1111473

« Prev Topic | Next Topic »

21 posts, Page 2 of 3

««1 2 3 »»

Permissions