How to get the line number Press 1, 2, 3, 4.... etc.

maxsteven

The following data table Sql2000 (the table name is TestTable) contains the following three columns
Number Name Other
10000 Beijing, A Dou falls with the wind
10000 Hebei sfsaadf
10000 Shanxi 23424234
10001 Eat with the wind
10001 sleep haha
10005 Don't think about it 2234234
10006 hurry up 3424234
10007 Follow the wind and spread the speed
10007 324 Follow the wind
... ... ......
How do I get the following data set
Sequence Number Name Other
1 10000 Beijing, A Dou falls with the wind
2 10000 Hebei sfsaadf
3 10000 Shanxi 23424234
1 10001 Eat with the wind
2 10001 sleep haha
1 10005 Don't think about it 2234234
1 10006 hurry up 3424234
1 10007 Follow the wind to spread the speed
2 10007 324 With the wind
... ... ... ...

That is to say, according to the number, each time it is reset to 1 to start the sorting, if the number changes, then to 1 to start the sorting
There are tens of thousands of records, which need to be completed in memory. How to write such Sql statements
The long-term confused problem must be solved now, please help solve it as soon as possible.

michealhenry

select
Order = (select count(1) from TestTable where number = a. number and name <= a. name),
Number, name, other
from TestTable a
order by number, order

michealhenry

--surroundings
create table TestTable
(
Number varchar(6),
Name varchar(10),
Other varchar(50)
)

insert into TestTable select '10000','Beijing','A Dou will fall with the wind'
insert into TestTable select '10000','河北','sfsaadf'
insert into TestTable select '10000','Shanxi', '23424234'
insert into TestTable select '10001','eat','safa with the wind'
insert into TestTable select '10001','sleep','haha'
insert into TestTable select '10005','Don't think about it', '2234234'
insert into TestTable select '10006','Hurry up', '3424234'
insert into TestTable select '10007','Faith with the wind','Speed ​​disperse each other'
insert into TestTable select '10007', '324','With the wind'

--Statement
select
Order = (select count(1) from TestTable where number = a. number and name <= a. name),
Number, name, other
from TestTable a
order by number, order

--result
Sequence Number Name Other
----------- ------ ---------- ----------------------- ---------------------------
1 10000 Beijing, A Dou falls with the wind
2 10000 Hebei sfsaadf
3 10000 Shanxi 23424234
1 10001 Eat with the wind
2 10001 sleep haha
1 10005 Don't think about it 2234234
1 10006 hurry up 3424234
1 10007 324 Follow the wind
2 10007 Follow the wind to spread the speed

(The number of rows affected is 9 rows)

maxsteven

Thank you so much, how can I only take the first item of each number

michealhenry

select number, name, other
from TestTable a
where not exists (select 1 from TestTable where number = a. number and name <a. name)

xiaoyueru

select number, name, other from TestTable where order=1