Update on Returning a Row Number in a Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Update on Returning a Row Number in a Query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-08-21 : 15:16:55

Mark writes "graz on 12/5/2000 12:01:00 AM in Queries wrote:

SELECT emp_id, lname, fname, job_id,
(SELECT COUNT(*) FROM employee e2 WHERE e2.lname <= e.lname AND e2.job_id = 10) AS rownumber
FROM employee e
WHERE job_id = 10
ORDER BY lname

This is an interesting option that bears a little closer scrutiny. It runs a count of records that have lname less than or equal to the current lname value. This works as long as lname is unique. Otherwise you get duplicate row numbers.

It works even if lname isn't unique if you do this:

SELECT emp_id, lname, fname, job_id,
(SELECT COUNT(*) FROM employee e2 WHERE ((e2.lname < e.lname) OR (e2.lname = e.lname AND e2.emp_id <= e.emp_id)) AND e2.job_id = 10) AS rownumber
FROM employee e
WHERE job_id = 10
ORDER BY lname"

Edited by - robvolk on 08/21/2001 15:21:11

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources