Home>

sql server - how to determine if a record with the same condition appears n times in an sql statement

How can I use SQL server to display the number of occurrences of a record with the same condition in SQLserver?
I can display the number of occurrences, but I want to display the number of occurrences for the record, as in the "nth" below.

↓ I want to get the number of times the same person has visited the same place. Time series is ascending ID

ID name Location nth
001 A-san Tokyo 1
002 B-san Osaka 1
003 C-san Tokyo 1
004 A-san Tokyo 2
005 C-san Tokyo 2
006 A-san Osaka 1
007 B-san Tokyo 1
008 C-san Tokyo 3
sql sqlserver
  • Answer # 1

    That ’s it.

    SELECT ID, name, location, ROW_NUMBER () OVER (PARTITION BY name, location ORDER BY ID)
FROM table
ORDER BY ID;

  • Answer # 2

    It seems to be easy if you use the ranking function prepared for each RDB
    It is like this when writing SQL general purpose

    create table tbl (id int primary key, name varchar (10), place varchar (10));
insert into tbl values
(1, 'A', 'Tokyo'),
(2, 'B', 'Osaka'),
(3, 'C-san', 'Tokyo'),
(4, 'A', 'Tokyo'),
(5, 'C', 'Tokyo'),
(6, 'Mr. A', 'Osaka'),
(7, 'B', 'Tokyo'),
(8, 'Csan', 'Tokyo');
select id, name, place,
(select count (*) + 1 from tbl
 where name = t1.name and place = t1.place and id

Related articles