A simple select? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

A simple select?

Author

Topic

lloydnicholson
Starting Member

5 Posts

Posted - 2002-11-04 : 05:47:48

I have two table - tblleaderboard and tblprizes
Tblleaderboard
id int
name varchar
total numeric

Tblprizes
id int
prize varchar

Now what I want to do is create a query that returns the whole tblleaderboard ordered by total desc but with the corresponding prize from the prize table.

There are 200 prizes in the table. The top prize has an id of 1, second prize is 2 and so on. This is as far as I can get...

SELECT A.ID, A.total, a.name, (SELECT COUNT(*) + 1 FROM Tblleaderboard B WHERE
B.total > A.total) RANK
FROM Tblleaderboard A
ORDER BY RANK;

My idea was to link the Rank to the tblprize.id in a sub query but I can't get it to list all of the leaderboard table.
Many thanks,

Lloyd

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-11-04 : 05:59:46

Hi

We did an article a while back that suits your question perfectly.
Take a look at this http://www.sqlteam.com/item.asp?ItemID=6398 and see if it helps.

Damian

lloydnicholson
Starting Member

5 Posts

Posted - 2002-11-04 : 06:16:22

Thanks for your help,

Lloyd

Edited by - lloydnicholson on 11/04/2002 06:54:07

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources