Strange SELECT statement

#21

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" <deiny (AT) nnn (DOT) com> wrote

Quote:

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Strange SELECT statement

microsoft.public.sqlserver.clients microsoft.public.sqlserver.clients

Re: Strange SELECT statement - 02-25-2008 , 07:01 PM