SQL Question!?

Question

Is it possible to update rows in a table based on what order that are sorted by......For example: say I have a row in my table which is a timestamp and a row in my table that is an integer(points). What I want to so is sort all results by timestamp and apply this formula to points.(Points=points+(101 - whatOrderYouAreListed)). It is hard to explain, but if you have the lowest timestamp, you get 100 points. If you have the second lowest timestamp, you get 99 points...and so on. Thank you for any help!

Answer 1

Add an identity field to your table. This is a field that automatically counts up with each new record added sequentially. If your table is replicated across multiple databases, then you may need to create a stored procedure to return your table. In your stored procedure, you'd create your query to return your data descending. Insert the results from the query into a temporary table you create in the stored procedure with the identity field. Use the identity to calculate points.

You could even have the stored procedure return the result with the calculated points.

Answer 2

Yes, you use a self join.

I'll show you how to get rank for a sample table.

select a1.username, count(a2.timestamp) as TimeRank from users a1, users a2 where a1.timestamp >= a2.timestamp group by a1.timestamp


You should be able to incorporate that into an update query to suit your needs.