Grouping and numbering problems

zhaoyu2002

The existence table table1 is structured as follows
CW Depth1 Depth2 ....
A 2500 2512
B 2511 2513
A 2711 2080
A 2900 2901
B 1153 1787
Want to group CWs, sort by Depth1
Then add the auto numbering in the group
as follows
CW Depth1 Depth2 ID
A 2500 2512 1
A 2711 2080 2
A 2900 2901 3
B 1153 1787 1
B 2511 2513 2

How to solve the above problems with Access

cairui1

SELECT *, (select count (*) from a56 where a.cw = cw and a.Depth1> = Depth1) from a56 a order by cw, Depth1

liuzhaoge

declare @temptable table (
    CW varchar (2),
    Depth1 int,
Depth2 int
)
insert @temptable select 'A', 2500,2512
union select 'B', 2511,2513
union select 'A', 2500,2512
union select 'A', 2711,2080
union select 'A', 2900,2901
union select 'B', 1153,1787


select cw, Depth1, Depth2, ID = (select count (*) from @temptable b where a.depth1> = b.depth1 and a.cw = b.cw group by cw) from @temptable a order by cw, Depth1


result
/ *
CW Depth1 Depth2 ID
A 2500 2512 1
A 2711 2080 2
A 2900 2901 3
B 1153 1787 1
B 2511 2513 2
* /

zhaoyu2002

I just used the method provided bycairui1, which can be used to get normal results, but it seems that the efficiency is very low. It took at least half a minute to check the wrong 200 data.