SQL Update each record with its position in an ordered select

Question

I'm using Access via OleDb. I have a table with columns ID, GroupID, Time and Place. An application inserts new records into the table, unfortunately the Place isn't calculated correctly.

I want to update each record in a group with its correct place according to its time ascending.

So assume the following data:

ID     GroupId  Time   Place
Chuck  1        10:01  2
Alice  1        09:01  3
Bob    1        09:31  1

should result in:

ID     GroupId  Time   Place
Chuck  1        10:01  3
Alice  1        09:01  1
Bob    1        09:31  2

I could come up with a solution using a cursor but that's AFAIK not possible in Access.

Answer 1

I just did a search on performing "ranking in Access" and I got this support.microsoft result.

It seems you create a query with a field that has the following expression:

Place: (Select Count(*) from table1 Where [Time] < [table1alias].[Time]) + 1

I can't test this, so I hope it works. Using this you may be able to do (where queryAbove is the above query):

UPDATE table1
SET [Place] = queryAbove.[Place]
FROM queryAbove
WHERE table1.ID = queryAbove.ID

It's a long shot but please give it a go.

Answer 2

I don't think time is a number or time formatted column, time is unfortunately a text string containing the numbers and dilimetrs of the time format. This is why sorting after the time column is illegal. Removing the dilimiters ":" and "," casting to integer and then sorting numirically could do the job