[Resolved] Pure MSSql Row Counter - possible??

[wdglide]

I m trying to accomplish a MS-SQL view (unable to do it on the frontend) that inserts a "seniority" column alongside other personnel data.

This code puts a zero in each row.

SELECT pers_id, pers_lastname, pers_hiredate, @@rowcount Total,

-- my problem starts here
(SELECT COUNT(*) FROM tblPersonnel i 
  WHERE pers_hiredate < i.pers_hiredate
  AND pers_active = 1 -- only active employees
  AND pers_unit_id = 6 -- work in unit 6
  AND pers_pos_id = 6 -- salespeople only
  ) Seniority
-- my problem ends here

FROM tblPersonnel
WHERE pers_active = 1 -- only active employees
  AND pers_unit_id = 6 -- work in unit 6
  AND pers_pos_id = 6 -- salespeople only
ORDER BY pers_hiredate, pers_id, pers_lastname

The select statement is filtered and sorted in such a way that if I were able to simply put a row counter in each row I could eliminate my sub-select statement.

Does anyone know how to put a row counter on the result set?

[Lars_Berg]

SELECT pers_id, pers_lastname, pers_hiredate, @@rowcount Total,

-- my problem starts here
(SELECT COUNT(*) FROM tblPersonnel 
  WHERE pers_hiredate < i.pers_hiredate
  AND pers_active = 1 -- only active employees
  AND pers_unit_id = 6 -- work in unit 6
  AND pers_pos_id = 6 -- salespeople only
  ) Seniority
-- my problem ends here

FROM tblPersonnel i
WHERE pers_active = 1 -- only active employees
  AND pers_unit_id = 6 -- work in unit 6
  AND pers_pos_id = 6 -- salespeople only
ORDER BY pers_hiredate, pers_id, pers_lastname

[wdglide]

I hate when people make my problems seem so trivial.

THANKS!!!