SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL Challenge #1


T-SQL Challenge #1

Author
Message
RBarryYoung
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14666 Visits: 9518
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."
RBarryYoung
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14666 Visits: 9518
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."
RBarryYoung
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14666 Visits: 9518
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."
David in .AU
David in .AU
SSChasing Mays
SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)

Group: General Forum Members
Points: 600 Visits: 561
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.
paul_ramster
paul_ramster
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 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


Ashish Gilhotra
Ashish Gilhotra
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 65
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85426 Visits: 41078
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. :-P

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
AlexT-359331
AlexT-359331
SSC-Enthusiastic
SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)SSC-Enthusiastic (157 reputation)

Group: General Forum Members
Points: 157 Visits: 596
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)
RBarryYoung
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14666 Visits: 9518
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."
Vamsee Krishna A
Vamsee Krishna A
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search