Can we generate Sequence number alon with Query 2

[narenkv]

Can we generate sequence number along with query

Example: In SQL server we have data like :-

naren india
rosy america
vinu sweeden

I need the query out put like

1 naren india
2 rosy america
3 venu sweeden

is it possible get (1,2,3....) with records in Sqlserver

Thanks again

Naren

 

[hmckillop]

No easy way of doing it in a select. You can auto increment in updates, I have shown this below along with a way of doing what you require - probably there is a more efficient way

DECLARE @v_myTempTable TABLE (iD BIGINT , LastName varchar(100), Firstname varchar(100))
DECLARE @v_i bigint

INSERT into @v_myTempTable (Description)
SELECT FirstName, LastName from <TableName> Order by LastName, Firstname

SET @v_i = 0

Update @v_myTempTable 
SET @v_i = @v_i + 1, 
	ID = @v_i
	

Select * from @v_myTempTable

"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[narenkv]

Thanks hmckillop

I got the solution in other beautiful way , I hope it will be helpfull to others also. Let me share this to all

select
(select count(*) from tbl_participant a where a.participant_id <= b.participant_id )slno,
* from tbl_participant b

Thanks
Naren

 

[dbomrrsm]

Nice one narenkv

only point I would add is that you may need to include a line:

ORDER BY slno

otherwise the results returned may not be in slno order

DBomrrsm