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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-07-14 : 07:26:20
|
| Jeroen writes "Hi,On my site you can earn points for predicting soccer games. Of course there's a score on my site, but it's not real time. I want to have a query that returns the position of a user in the real time score without having to loop through the recordset until I found the right user.Now I create a temp table with a column with identity insert, but I don't like this solution, because a temp table is not real time. Is it possible to make a view or query with a column that behaves like an identity insert, so i get a result like:number name id points1 Frank 1 1002 Bla 2 953 Peter 3 93...with this result I can say "SELECT number FROM x WHERE id = 1" and I get the right position in the score. I use SQL 7. Good luck :-)regards,Jeroenwww.voetbalpoules.nl (in dutch)" |
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-07-14 : 08:18:07
|
| As I know you can't avoid using an auxiliary table.- Vit |
 |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-07-14 : 08:59:34
|
| Why can't you just select the id?-------Moo. :) |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-07-14 : 09:14:06
|
| This is something like what you are looking for?SELECT (SELECT COUNT(*) FROM tblPoints aWHERE a.Points > b.Points) + 1 AS Ranking,Name, MemberID, PointsFROM tblPoints b ORDER BY b.PointsDo watch out for ties!Owais |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-07-14 : 12:42:25
|
quote:
Now I create a temp table with a column with identity insert, but I don't like this solution, because a temp table is not real time.
Unless your soccer players are taking heavy amphetamines, they are unlikely to score a goal in the 0.01 seconds it takes to select from your table and put it into a temp table. OK, MAYBE it might take up to 1 second to do it. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-14 : 13:05:50
|
| AND...with that prophetic insight....7000Brett8-) |
|
|
|
|
|
|
|
|
Retrieving position in a result set