SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Reader Challenge #2 Solutions
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 10/28/2001 :  23:59:07  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
This challenge was certainly more popular than our last one. I received over 70 solutions! That in spite of my not knowing how rank golf handicaps. Congratulations to our own Nigel Rivett (nr in the forums) for the first correct solution. I'll break down his solution and correct my error with golf handicaps.

Article Link.

fisherman_jake
Slave to the Almighty Yak

Australia
159 Posts

Posted - 10/29/2001 :  00:47:43  Show Profile  Send fisherman_jake a Yahoo! Message  Reply with Quote
Just out of curiosity I ran nr's solution against the one I submitted, and I couldn't believe the results. I was expecting nr's to be faster having less steps and all, but as it turned out nr's solution relative to the batch is 56.37%(8 steps) and mine was 43.63(11 steps).. Check it out yourself, I may have an idea why this is so..

 
--<TSQL>--
/*

if exists (select * from dbo.sysobjects where id = object_id(N'[SQLTeam_Golfers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [SQLTeam_Golfers]
-- GO

-- Create the Table
Create Table SQLTeam_Golfers(
Player VarChar(20) NOT NULL PRIMARY KEY,
hcap int NOT NULL
)

-- Add some test data
INSERT INTO SQLTeam_Golfers VALUES ('Bob', 3)
INSERT INTO SQLTeam_Golfers VALUES ('Tim', 5)
INSERT INTO SQLTeam_Golfers VALUES ('Jo', 5)
INSERT INTO SQLTeam_Golfers VALUES ('Fred', 8)

--Some more test data
INSERT INTO SQLTeam_Golfers VALUES ('Barry', 4)
INSERT INTO SQLTeam_Golfers VALUES ('Tony', 5)
INSERT INTO SQLTeam_Golfers VALUES ('John', 1)
INSERT INTO SQLTeam_Golfers VALUES ('Frank', 3)

*/

-- my solution
Select
A.Player,
A.Hcap,
B.Rank
from
SQLTeam_Golfers A
Left Join
(
select
C.hcap,
Rank = (select Count(D.hcap) from SQLTeam_Golfers D Where D.hcap < C.Hcap ) + 1
from (select Distinct hcap From SQLTeam_Golfers) C
) B
On
A.Hcap = B.Hcap
Order By B.Rank

-- nr's solution
select g1.Player,
g1.hcap,
Rank = (select count(*) + 1
from SQLTeam_Golfers g2
where g1.hcap < g2.hcap)
from SQLTeam_Golfers g1
order by Rank

--<TSQL>--




==================================================
World War III is imminent, you know what that means... No Bag limits!!!
Master Fisherman
Go to Top of Page

Nazim
A custom title

United Arab Emirates
1408 Posts

Posted - 10/29/2001 :  01:40:51  Show Profile  Reply with Quote
hey! Guess what i had got a xactly same solution after i read this article by graz ...What After Top?
http://www.sqlteam.com/item.asp?ItemID=566.

but thought this couldnt be so simple


-------------------------
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 10/29/2001 :  01:52:34  Show Profile  Reply with Quote
What happened to the Yakiest and Ugliest solutions?

DavidM
Look, over the horizon..."Tutorial D"
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 10/29/2001 :  05:28:31  Show Profile  Visit Merkin's Homepage  Reply with Quote
David, you really don't want to see them. I saw one, it was pretty bad. You wouldn't like it at all



Damian
Go to Top of Page

ghreddy
Starting Member

1 Posts

Posted - 10/29/2001 :  06:42:52  Show Profile  Reply with Quote
Hi every one,
check out the devx tip that has been published 2 months back.
"A Ranking Algorithm Implemented In One SQL Statement"
http://www.devx.com/free/tips/tipview.asp?content_id=3242

I think one of the easiet Reader challenges..not a tough enough..



Go to Top of Page

chaugner
Starting Member

USA
9 Posts

Posted - 10/29/2001 :  07:58:36  Show Profile  Send chaugner an AOL message  Send chaugner a Yahoo! Message  Reply with Quote
Here is the fastest solution .. yeap faster then the winner.

select sg1.Player,
sg1.hcap,
Count(sg2.hcap) + 1 'Rank'
from SQLTeam_Golfers sg1
left join SQLTeam_Golfers sg2
on sg1.hcap > sg2.hcap
group by sg1.Player, sg1.hcap
order by [Rank]

Go to Top of Page

ToddV
Posting Yak Master

USA
218 Posts

Posted - 10/29/2001 :  09:08:07  Show Profile  Reply with Quote
Right on chaugner,

I posted avery similar solution and I also find it to be better than the published solution.


Go to Top of Page

royv
Constraint Violating Yak Guru

455 Posts

Posted - 10/29/2001 :  09:11:44  Show Profile  Reply with Quote
I am in agreement with chaugner on this one. A join should always be better then a correlated sub-query. Graz???

Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 10/29/2001 :  09:14:51  Show Profile  Visit graz's Homepage  Reply with Quote
What I posted was the first correct answer. I asked you guys to figure out if it was the fastest. It sounds like it isn't. So...how much faster is a join? Come on...show me some query plans :) Why is it faster?

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 10/29/2001 :  12:52:28  Show Profile  Visit nr's Homepage  Reply with Quote
Anyone tried putting an index on hcap - probably non-clustered would be best.
Shouldn't make much difference for this data so maybe populate the table with more rows.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.
Go to Top of Page

JohnDeere
Posting Yak Master

USA
191 Posts

Posted - 10/29/2001 :  14:15:45  Show Profile  Reply with Quote
I ran the winning solution and the join solution as a batch. The winning solution had a cost of 49.89% (9 steps) and the join solution had a cost of 50.11% (8 steps)relative to the batch.

LHarra
"They that can give up essential liberty to obtain a little temporary safety
deserve neither liberty nor safety."
(Benjamin Franklin)

Go to Top of Page

fisherman_jake
Slave to the Almighty Yak

Australia
159 Posts

Posted - 10/29/2001 :  16:50:56  Show Profile  Send fisherman_jake a Yahoo! Message  Reply with Quote
Which still leaves my solution with 11 steps faster than nr's 8 step(56.37%) solution at 43.63% relative to the batch. I tried to get it lower than 43.63% but have no luck.. Maybe there is another solution below 40%.. Now that's the challenge.. Also here is the query plan, my solution vs nr's.. :


|--Sort(ORDER BY:([Expr1006] ASC))
|--Merge Join(Right Outer Join, MERGE:([SQLTeam_Golfers].[hcap])=([A].[Hcap]), RESIDUAL:([A].[Hcap]=[SQLTeam_Golfers].[hcap]))
|--Compute Scalar(DEFINE:([Expr1006]=[Expr1004]+1))
| |--Nested Loops(Inner Join)
| |--Sort(DISTINCT ORDER BY:([SQLTeam_Golfers].[hcap] ASC))
| | |--Clustered Index Scan(OBJECT: ([Central].[dbo].[SQLTeam_Golfers].[PK__SQLTeam_Golfers__2B354DF6]))
| |--Stream Aggregate(DEFINE:([Expr1004]=Count(*)))
| |--Clustered Index Scan(OBJECT: ([Central].[dbo].[SQLTeam_Golfers].[PK__SQLTeam_Golfers__2B354DF6] AS [D]), WHERE:([D].[hcap]<[SQLTeam_Golfers].[hcap]))
|--Sort(ORDER BY:([A].[Hcap] ASC))
|--Clustered Index Scan(OBJECT: ([Central].[dbo].[SQLTeam_Golfers].[PK__SQLTeam_Golfers__2B354DF6] AS [A]))

(10 row(s) affected)


nr's:
|--Sort(ORDER BY:([Expr1004] ASC))
|--Compute Scalar(DEFINE:([Expr1004]=[Expr1002]+1))
|--Nested Loops(Inner Join)
|--Clustered Index Scan(OBJECT: ([Central].[dbo].[SQLTeam_Golfers].[PK__SQLTeam_Golfers__2B354DF6] AS [g1]))
|--Table Spool
|--Stream Aggregate(DEFINE:([Expr1002]=Count(*)))
|--Clustered Index Scan(OBJECT: ([Central].[dbo].[SQLTeam_Golfers].[PK__SQLTeam_Golfers__2B354DF6] AS [g2]), WHERE:([g2].[hcap]>[g1].[hcap]))

(7 row(s) affected)



Sorry for the EXTENDED window.. wrapping it in code does that..

==================================================
World War III is imminent, you know what that means... No Bag limits!!!
Master Fisherman
Go to Top of Page

jdbaker01
Starting Member

USA
2 Posts

Posted - 10/30/2001 :  12:02:47  Show Profile  Send jdbaker01 a Yahoo! Message  Reply with Quote
Definitely, better and cleaner too. FYI: Chaunger is standing over my shoulder at the moment.

quote:

Here is the fastest solution .. yeap faster then the winner.

select sg1.Player,
sg1.hcap,
Count(sg2.hcap) + 1 'Rank'
from SQLTeam_Golfers sg1
left join SQLTeam_Golfers sg2
on sg1.hcap > sg2.hcap
group by sg1.Player, sg1.hcap
order by [Rank]





Go to Top of Page

fisherman_jake
Slave to the Almighty Yak

Australia
159 Posts

Posted - 10/30/2001 :  17:32:20  Show Profile  Send fisherman_jake a Yahoo! Message  Reply with Quote
jd & chaugner any chance of trying to get your solution running without the Warning: Null value eliminated from aggregate. and still not ruin the ranking. I tried to isolate it out, but it kept on pushing John's ranking to 2 which is not right, may be you have a better idea. Your solution is great, I would prefer it to get a clear run though..

==================================================
World War III is imminent, you know what that means... No Bag limits!!!
Master Fisherman
Go to Top of Page

chaugner
Starting Member

USA
9 Posts

Posted - 10/30/2001 :  21:01:30  Show Profile  Send chaugner an AOL message  Send chaugner a Yahoo! Message  Reply with Quote
even though I cheated a little bit you can get it to work like this without the sql error.

select Player, hcap, Sum(Total) + 1 'Rank'
from(select sg1.Player, sg1.hcap, Total = case when sg2.hcap is null then 0 else 1 end
from SQLTeam_Golfers sg1
left join SQLTeam_Golfers sg2
on sg1.hcap > sg2.hcap) t
group by t.Player, t.hcap
order by [Rank]


quote:

jd & chaugner any chance of trying to get your solution running without the Warning: Null value eliminated from aggregate. and still not ruin the ranking. I tried to isolate it out, but it kept on pushing John's ranking to 2 which is not right, may be you have a better idea. Your solution is great, I would prefer it to get a clear run though..

==================================================
World War III is imminent, you know what that means... No Bag limits!!!
Master Fisherman



Go to Top of Page

ToddV
Posting Yak Master

USA
218 Posts

Posted - 10/31/2001 :  12:13:24  Show Profile  Reply with Quote
No need for a subquery here:

select Player, hcap, Sum(case when sg2.hcap is null then 0 else 1 end) + 1 'Rank'
from SQLTeam_Golfers sg1
left join SQLTeam_Golfers sg2
on sg1.hcap > sg2.hcap
group by t.Player, t.hcap
order by [Rank]

Go to Top of Page

chaugner
Starting Member

USA
9 Posts

Posted - 11/01/2001 :  09:49:58  Show Profile  Send chaugner an AOL message  Send chaugner a Yahoo! Message  Reply with Quote
from jd & chaugner ... at work we work with case sensitive and insensitive database servers. The funny thing is that on a case insensitive server our solution was .01% slower than the winner. however on a case sensitive ours is twice as fast .. thats why we posted it as being the fastest :)

chaugner & jdbaker
(dbLlammas)

Go to Top of Page

jdbaker01
Starting Member

USA
2 Posts

Posted - 11/01/2001 :  11:01:03  Show Profile  Send jdbaker01 a Yahoo! Message  Reply with Quote
The case sensitive server we were using was a SQL Server 2000 with Latin_General_CS collation. Although the same exact performance was achieved on a Latin_General_BIN.

quote:

from jd & chaugner ... at work we work with case sensitive and insensitive database servers. The funny thing is that on a case insensitive server our solution was .01% slower than the winner. however on a case sensitive ours is twice as fast .. thats why we posted it as being the fastest :)

chaugner & jdbaker
(dbLlammas)





Forgive me father for I have hacked. . .
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000