June 26, 2003 at 6:59 am
Is there any way to do a select that selects a distinct field and then an auto incrementing number for each record?
Example:
Name AutoNum
------------------
Bill 1
Tom 2
Harry 3
Darren
Darren
June 26, 2003 at 7:30 am
Check out the IDENTITY function.
IDENTITY ( data_type [ , seed , increment ] )
You have to do a SELECT INTO statement.
SELECT Name,
IDENTITY(INTEGER, 1, 1)
INTO #TempTableName
FROM tablename
SELECT * FROM #TempTableName
June 26, 2003 at 8:10 am
Here is two different ways to do that.
create table test(Name varchar(10))
insert into test values('Bill')
insert into test values('Tom')
insert into test values('Dick')
insert into test values('Harry')
insert into test values('Greg')
insert into test values('Harry')
insert into test values('Bill')
-- ONE WAY
declare @t table(name varchar(10), AutoNum int identity)
insert into @t(name) select distinct name from test order by name
select * from @t
-- Another way
select A.name, count(*) as autonum from
(SELECT DISTINCT NAME FROM test) a,
(SELECT DISTINCT NAME FROM test) b
where a.name >= b.name
group by A.name
ORDER BY 1
drop table test
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
June 27, 2003 at 7:04 am
Slightly off the track, but if you didn't want a distinct search, and wanted to have an incremental field count WITHOUT using IDENTITY, then try this [assuming the above test table exists]:
-- Declarations
DECLARE@iCounterINT
DECLARE @t TABLE(FirstName VARCHAR(10), autonum INT NULL)
-- Initialisations
SET@iCounter = 0
-- Get all existing names from test
INSERT@t
SELECTName
,NULL
FROMtest
-- Insert the incremental count as required
UPDATE@t
SET@iCounter = autonum = @iCounter + 1
-- Return recordset
SELECT*
FROM@t
June 27, 2003 at 12:29 pm
Does it have to be incremental or just unique? If just unique, the selecting with a column newid() works beautifully.
Signature is NULL
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply