Record number?

[Cagliostro]

Hi,
how can I get the record number of query or a table without using cursors or temporary tables? <p>John Fill<br>
<img src=

http://ournet.md/~ifilipski/1c.bmp>

<br>
ivfmd@mail.md

 

[tlbroadbent]

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

http://www.sqlteam.com/ItemPrint.asp?ItemID=1491.

Post additional questions, if any, about this query and I'll try to answer them.

 

[Cagliostro]

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

http://ournet.md/~ifilipski/1c.bmp

ivfmd@mail.md

 

[ColinM]

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

 

[Cagliostro]

ColinM,
I've read already it in an other thread.
I want do get it in a select query, not in an update query. John Fill

http://ournet.md/~ifilipski/1c.bmp

ivfmd@mail.md

 

[tlbroadbent]

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.