Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations MikeeOK on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Record number?

Status
Not open for further replies.
Here is one possibility using a correlated subquery. It depends on having a unique index or key on your table. In this example EmpID is the unique column.

SELECT EmpID, EmpName, Dept,
(SELECT COUNT(*) FROM EmpTable e2 WHERE e2.EmpID<=e1.EmpID AND e2.Dept=2) AS RowNo
FROM EmpTable e1
WHERE e1.Dept=2
ORDER BY EmpID

In my test, the returned result was

[tt]
EmpID EmpName Dept RowNo
------- ----------------------- ----- -----
000011 SMITH, JOHN 2 1
000644 MCFADDEN, MARY 2 2
000729 BROWN, JULIETTE 2 3
000803 WILLIAMS, ALEXANDER 2 4
000885 LOPEZ, ROBERTA 2 5
000891 CZEKALA, STEFAN 2 6
000899 BONDS, BARRY 2 7
[/tt]

This approach may be very costly if the table contains a large number of records. In that case, a temporary table may provide a better solution.

This solution was originally provided by a reader on the SQLTeam website. The article can be read at
Post additional questions, if any, about this query and I'll try to answer them.
 
Thsnks a lot,
but the problem is what I do not have an unique index. The problems is to assign it to duplicates.

This is a not distinct select. I can not distinct duplicates between themselves:
EmpName
-----------------------
SMITH, JOHN
SMITH, JOHN
SMITH, JOHN
SMITH, JOHN
WILLIAMS, ALEXANDER
BROWN, JULIETTE
BROWN, JULIETTE

This is what I want to get is:
EmpName RecordID
----------------------- ----
SMITH, JOHN 1
SMITH, JOHN 2
SMITH, JOHN 3
SMITH, JOHN 4
WILLIAMS, ALEXANDER 5
BROWN, JULIETTE 6
BROWN, JULIETTE 7

The fastest way what I use is to make temporary tables with autonumbers(identity). But I want a better way. John Fill

ivfmd@mail.md
 
You could use something like this
select name,identity(int,1,1) as recordID into #temp from employees
All select into operations are non-logged so are fast, but make sure you've got a complete database backup

or you could add a column to your existing table
then

declare @variable int
set @variable = 0
update employees
SET @variable = recordID = @variable + 1

stolen from malcolmw faq
faq183-207
 

If you have no unique column, then the only choice available in SQL Server is to create a temporary table or use a cursor. Use of a temporary table is usually preferred to the use of cursors. SQL Server can actually handle that quite efficiently if the procedure is correctly designed.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top