October 30, 2002 at 11:20 am
Hi everyone
First of all excuse my bad english, I hope you understand me.
I' searching for a function or something else that allow to return the progressive number in a select statement.
Example:
CREATE TABLE Employees
( Name varchar (50),
Surname varchar (50)
)
GO
INSERT INTO Employees (Name, Surname)
VALUES ('Mario', 'Rossi')
GO
INSERT INTO Employees (Name, Surname)
VALUES ('Billy', 'Joe')
GO
INSERT INTO Employees (Name, Surname)
VALUES ('Jules', 'DeCarmagnac')
GO
SELECT Name, Surname, FUNCTION() AS Id
FROM Employees
/* Where function() is a name of a magic word that produce a progressive number */
GO
EXPECTED Result Set
NAME SURNAME Id
Mario Rossi 1
Billy Joe 2
Jules DeCarmagnac 3
Is it impossible ?
I can't use a user table within a column defined identity and I would avoid to create a temporarily table within a column defined identity and so make a select over it.
Can someone help me ?
Thank's in advance,
Marco.
"...e il mio maestro mi insegnò com'è difficile trovare l'alba dentro l'imbrunire" F.Battiato - Prospettiva Nevsky
October 30, 2002 at 12:02 pm
would it help if you did this:
SELECT Name, Surname, (Max(Id) + 1) as ID
FROM Employees
This would aleays give you 1 + your max value in the table..
Or have you tried to use the Identity option when creating your table?
--> David V.F. Burton <--
--> David V.F. Burton <--
http://www.siantrex.net
October 31, 2002 at 1:52 am
I don't have any column Identity on the table and I can't add one, because the base of number must be set to 0 every time that a user make an insert in the table.
Excuse me a simple question, the keyword Id in your post is a name of a column in emloyees, right ?
"...e il mio maestro mi insegnò com'è difficile trovare l'alba dentro l'imbrunire" F.Battiato - Prospettiva Nevsky
October 31, 2002 at 7:37 am
Try this, I changed the real tables temp tables (The "IDENTITY" Function only work with "INTO"):
CREATE TABLE #Employees
( [Name] varchar (50),
Surname varchar (50)
)
INSERT INTO #Employees ([Name], Surname)
VALUES ('Mario', 'Rossi')
INSERT INTO #Employees ([Name], Surname)
VALUES ('Billy', 'Joe')
INSERT INTO #Employees ([Name], Surname)
VALUES ('Jules', 'DeCarmagnac')
SELECT [Name], Surname, IDENTITY(INT,1,1) AS [Id]
INTO #work
FROM #Employees
SELECT * FROM #work
-- DROP TABLE #employees YOU WON'T WANT TO DROP THIS OF COURSE...
DROP TABLE #work
-Dan
Edited by - dj_meier on 10/31/2002 07:38:15 AM
Edited by - dj_meier on 10/31/2002 07:39:15 AM
-Dan
October 31, 2002 at 9:00 am
Thanks for your post, the script works fine.
May be it is not a good idea for performance (I have above 2500 records) but it work well.
Thank's!
"...e il mio maestro mi insegnò com'è difficile trovare l'alba dentro l'imbrunire" F.Battiato - Prospettiva Nevsky
October 31, 2002 at 2:02 pm
One thing to mention if performance is an issue...
You could use a Table Variable instead of a temp table. Table Variables are stored in memory instead of tempdb. This will increase performance.
You would need to create a local table variable with an idenetity column and then insert into it like this:
CREATE TABLE Employees
( [Name] varchar (50),
Surname varchar (50)
)
-- Create Table Variable Here---v
DECLARE @work TABLE([Name] VARCHAR(50), Surname VARCHAR(50), [ID] INTEGER IDENTITY)
INSERT INTO Employees ([Name], Surname)
VALUES ('Mario', 'Rossi')
INSERT INTO Employees ([Name], Surname)
VALUES ('Billy', 'Joe')
INSERT INTO Employees ([Name], Surname)
VALUES ('Jules', 'DeCarmagnac')
INSERT @work
SELECT [Name], Surname
FROM Employees
SELECT * FROM @work
-Dan
-Dan
November 4, 2002 at 1:44 am
This work right!! This method have serious increased the performance! Thank you very much!
"...e il mio maestro mi insegnò com'è difficile trovare l'alba dentro l'imbrunire" F.Battiato - Prospettiva Nevsky
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply