sql - Add sequence number to query results -


i using access 2010 process student discipline information data.

for incidents there multiple offenders. in source data each offender has own row. need query return offender number (1, 2, 3) involved in incident.

the data in single table each row has unique key tying rest of database.

source table:

incidentid_uk    |    personid_uk    |   uniquekey  ------------------------------------- 3001             |    1234           |    1 3002             |    1235           |    2 3002             |    1236           |    3 3002             |    1237           |    4 3003             |    1238           |    5 3004             |    1239           |    6 3005             |    1234           |    7 

desired result:

incidentid_uk    |    personid_uk    |    sequence ------------------------------------- 3001             |    1234           |    1 3002             |    1235           |    1 3002             |    1236           |    2 3002             |    1237           |    3 3003             |    1238           |    1 3004             |    1239           |    1 3005             |    1234           |    1 

in ms access, can information using correlated subquery:

select incidentid_uk, personid_uk,        (select count(*)         table t2         t2.incidentid_uk = t.incidentid_uk ,               t2.personid_uk <= t.personid_uk        ) sequence table t; 

Comments

Popular posts from this blog

c++ - Difference between pre and post decrement in recursive function argument -

php - Nothing but 'run(); ' when browsing to my local project, how do I fix this? -

php - How can I echo out this array? -