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 Monday, September 14, 2009 11:42 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,294, Visits: 9,480
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
Posted Monday, September 14, 2009 11:48 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,294, Visits: 9,480
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 #787905
Posted Tuesday, September 15, 2009 12:31 AM


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,294, Visits: 9,480
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 #787921
Posted Tuesday, September 15, 2009 12:40 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 6:47 PM
Points: 523, Visits: 557
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
Posted Tuesday, September 15, 2009 2:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, January 17, 2014 6:38 AM
Points: 278, Visits: 534
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
Posted Tuesday, September 15, 2009 3:41 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 5:48 AM
Points: 50, Visits: 47
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
Posted Tuesday, September 15, 2009 6:09 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:18 PM
Points: 35,267, Visits: 31,759
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?


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.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #788083
Posted Tuesday, September 15, 2009 9:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 10:23 AM
Points: 149, Visits: 591
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
Posted Tuesday, September 15, 2009 1:27 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,294, Visits: 9,480
Ouch! I just realized that I triple-posted the other day when I was having a lot of trouble with the site...

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #788493
Posted Wednesday, May 18, 2011 11:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase ««123»»

Permissions Expand / Collapse