How to Implement rownum (pseudo row number) in access query.

Many of my friends have encountered this problem. If a row number is generated in the query. For example, see the following table. Tbla ID, cname, score 1 a001 80 2 B002 71 3 c003 92 4 e004 80 5 d005 85 How can I rank by score? The access SQL statement does not contain pseudo-column functions such as rownum. However, in addition to using programs, you can also use the following SQL query statement to implement 1. sort by score, and the parallel operator will extend down (there is no third name, and there will be two fourth places in the column below ). Id cname score SnO 3 c003 92 1 5 d005 85 2 4 e004 80 4 1 a001 80 4 2 B002 71 5 Select a. ID, A. cname, A. Score, count (*) as SnO From tbla a inner join tbla B on A. score <= B. Score Group by A. ID, A. cname, A. Score Order by A. Score DESC 2. sort by score, and the columns are listed in the descending order (there is no fourth place, and there are two third places in the column below ). Id cname score SnO 3 c003 92 1 5 d005 85 2 4 e004 80 3 1 a001 80 3 2 B002 71 5 Select a. ID, A. cname, A. Score, count (B. ID) + 1 as SnO From tbla a left join tbla B on A. score <B. Score Group by A. ID, A. cname, A. Score Order by A. Score DESC 3. sort by score, and the students with the student ID are listed first. Id cname score SnO 3 c003 92 1 5 d005 85 2 4 e004 80 3 1 a001 80 4 2 B002 71 5 Select a. ID, A. cname, A. Score, count (*) as SnO From tbla a inner join tbla B on (A. score <B. score or (A. Score = B. Score and A. ID <= B. ID )) Group by A. ID, A. cname, A. Score Order by 4 The above are standard SQL query statements. In addition, you can use the dcount Domain Function in the access environment to implement this sort number. The advantage of using the dcount domain function is that this query is still updatable. But only available in the access environment. If you use ADO, Dao won't go through the JET-SQL engine. For example, you are in ASP. I, Select ID, cname, score, dcount ('id', 'tbla ', 'score> =' & score) as SnO From tbla Order by 4 II, Select ID, cname, score, dcount ('id', 'tbla ', 'score>' & score) + 1 as SnO From tbla Order by 4 III, Select ID, cname, score, dcount ('id', 'tbla ', 'score> '& score &' or (score = '& score &' and ID> = '& ID &') as SnO From tbla Order by 4