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 - 2002-06-27 : 09:39:15
|
Pedro writes "i want to make a ranking and update a table that hold the position from several courses in a school with hundreds students. exampleCourse Stu.Name Points Position Chemistry Jhon 19.99 1 Chemistry Mary 19.80 2 Chemistry Lani 16.25 3 Math Gustav 17.75 1 Math Silvia 15.25 2 and so on Thanks and i apologize for my english" |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-27 : 10:18:54
|
| You are going to need to provide the exact ddl (create table statements) for the tables involved in order for us to provide exact sql. However, you will need a subquery for the positions column that generates the position for the same course, based on points.<O> |
 |
|
|
MakeYourDaddyProud
184 Posts |
Posted - 2002-06-27 : 10:23:15
|
| Assume Results is the tablename, I have derived the ranking from an inner count query. This can then be wrpped in an update on joins to Course and Student name to populate your Ranking column on your table.SELECT R.*,R3.RankingFROM #Results RJOIN (SELECT Course, StudentName, Points, (SELECT count(*) FROM #Results R1 WHERE R2.Points <= R1.Points AND R2.Course = R1.Course) AS Ranking FROM #Results R2 ) AS R3ON R.course = R3.CourseAND R.StudentName = R3.StudentNameORDER BY R.Course, R3.RankingDaniel Small MIAPwww.danielsmall.com IT Factoring |
|
|
|
MakeYourDaddyProud
184 Posts |
Posted - 2002-06-27 : 10:52:30
|
| Can you provide some DDL. I can then optimize the UPDATE for your, cheers.Daniel Small MIAPwww.danielsmall.com IT Factoring |
|
|
|
|
|
|
Ranking for position