June 19, 2006 at 3:16 pm
Hi!
I need to return like a line number of the result of my Select. How can I do it?
Example:
select (line_number??), FirtsName, LastName
from tb Employees
Result:
1 Tere Castro
2 Jose Lopez
3 Mario Casas
4 Miguel Dias
June 19, 2006 at 3:52 pm
Have a look at this article:
http://www.databasejournal.com/features/mssql/article.php/3572301
June 19, 2006 at 10:37 pm
For those that want the hard and slow way to do this, here is a cursor that will number each row as it is produced in the result set. The Row_Number function and the Rank function in SQL 2005 make this far easier to accomplish.
Declare
@rcount int
Declare
@EMPID int
Declare
@Fname Varchar(25)
Declare
@LName varchar(50)
Declare
@bd smalldatetime
DECLARE
Employee_Cursor CURSOR FOR
SELECT
EmpID, Fname, Lname, bd
From
Employee
OPEN
Employee_Cursor
SET
@rcount=0
FETCH
NEXT FROM Employee_Cursor
INTO
@EMPID, @FName, @Lname, @bd
WHILE
@@FETCH_STATUS = 0
BEGIN
Print 'RowNum is ' + Cast(@rcount as Varchar(3)) + ' The row is ' + Cast(@EmpID as Varchar(3)) + ', ' + @Fname + ', ' + @Lname + ' and ' +
Cast(@bd as varchar(12))
Set @rcount=@rcount+1
FETCH NEXT FROM Employee_Cursor
INTO @EMPID, @FName, @Lname, @bd
END
CLOSE
Employee_Cursor
DEALLOCATE
Employee_Cursor
GO
June 20, 2006 at 3:13 pm
Try this
CREATE table #NumberName(LineNumber int not null identity(1,1),firstname varchar(25),surname varchar(50))
insert into #NumberName(firstname,surname)
select firstname,surname
from tbl_employees
order by surname,firstname
select LineNumber,firstname,surname
from #NumberName
order by LineNumber
June 21, 2006 at 1:28 pm
This is one of the new feature of SQL2005 and no kludge or workaround is required.
declare @Employees table (firstname varchar(20), lastname varchar(20))
insert @employees values('Jane', 'Doe')
insert @employees values('John', 'Doe')
insert @employees values('Ed', 'Smith')
insert @employees values('Rob', 'Smith')
select ROW_NUMBER() OVER (order by Lastname, firstname) as RowNumber, FirstName, LastName
from @Employees
June 21, 2006 at 4:09 pm
Using an identity column will work fine until you have deleted a number of rows, then the line numbers will no longer be sequential.
Keith
June 21, 2006 at 4:42 pm
The temp table numbering is not affected by deletions in the source table.
If you need to "delete" rows from the output this could be done in the where clause of the insert or by using joins, or from a intermediary temp table. In any case once you know which rows you want to output, it can be done with the above script.
I'm obviously not expecting the source data to be stored with "line numbers" as the original question was how to produce the output from a select statement.
Cheers
June 21, 2006 at 6:57 pm
Since this is SQL2005, we should be using the features of 2005. There is no need for temp tables or loops to do this anymore.
June 21, 2006 at 7:30 pm
Sorry, I didn't even look at the section this was posted under.
The SQL2005 functionality would be the best way to go.
June 22, 2006 at 8:43 am
Istill don't have a SQL Server 2005, I am using SQL 2000.
June 22, 2006 at 8:46 am
Then why did you post to a SQL 2005 forum?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply