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
Post a Comment