Strange SELECT statement

Assuming you have a table like the following:

CREATE TABLE #emp ([Name] VARCHAR(20));
GO

INSERT INTO #emp ([Name]) VALUES ('Peter');
INSERT INTO #emp ([Name]) VALUES ('John');
INSERT INTO #emp ([Name]) VALUES ('Daniel');
INSERT INTO #emp ([Name]) VALUES ('Bob');
GO

SQL 2005 solution:

SELECT [Name], ROW_NUMBER() OVER (ORDER BY [Name]) * 5 AS SpecialNo
FROM #emp;
GO

SQL 2000 solution:

SELECT e1.[Name], COUNT(*) * 5 AS SpecialNo
FROM #emp e1
INNER JOIN #emp e2
ON e2.[Name] <= e1.[Name]
GROUP BY e1.[Name]
GO


"Deiny García Pérez" wrote in message

> Hello Everyone...
>
> I have a particular Select statement to do... for example I want a
> Select like this:
>
> Select Name, SpecialNo from emp... etc. and get result like this:
>
> Name SpecialNo
> Peter 5
> John 10
> Daniel 15
> Bob 20
>
> But the problem is this, I need SpecialNo to be a generated number, not a
> real exixting data and also I want this number to increment in every row
> (in this case is like John's SpecialNo = Peter's SpecialNo + 5 and so on)
>
> Please, help me, thanks in advance
>
 >> Stay informed about: Strange SELECT statement