dynamic/temporary autoincrement in SELECT statemen

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

dynamic/temporary autoincrement in SELECT statemen

Author

Topic

axisperfect
Starting Member

21 Posts

Posted - 2004-09-21 : 23:29:29

Help! Need a little bit of creativity here. My users have requested I export some data in a rather arbitrary format in one single CSV file--one criteria is how they want to auto-number some of the rows:

LineNumber,Type,Date,SomeData1,SomeData2,SomeData2
1,TypeA,20-Sept-2004,ABC,XYZ,123
2,TypeA,20-Sept-2004,ABC,XYZ,123
3,TypeA,20-Sept-2004,ABC,XYZ,123
4,TypeA,20-Sept-2004,ABC,XYZ,123
1,TypeB,20-Sept-2004,ABC,XYZ,123
2,TypeB,20-Sept-2004,ABC,XYZ,123
1,TypeC,20-Sept-2004,ABC,XYZ,123
2,TypeC,20-Sept-2004,ABC,XYZ,123
3,TypeC,20-Sept-2004,ABC,XYZ,123
4,TypeC,20-Sept-2004,ABC,XYZ,123

Note that the line number "resets" itself every time the type of row is different... I'm thinking if I can get a select statement for each type (with autoincrement), I can union the select statements and get what I want.

So... any ideas for a way to generate a SQL statement that will do something like:
select (auto_increment_in_this_statement_only), (my_other_fields) from my_table where my_criteria

Sample Table NAMES, with field FName
John Doe
Jane Doe
Billy Bob
Ray Bob


Select auto_number, NAMES from FName order by FName

would result in
1, Billy Bob
2, Jane Doe
3, John Doe
4, Ray Bob


Select auto_number, NAMES from FName order by FName DESC

would result in
1, Ray Bob
2, John Doe
3, Jane Doe
4, Billy Bob

ffoiii
Starting Member

12 Posts

Posted - 2004-09-22 : 00:00:55

If you put all records into a table with a single ident column and then sort by an alternate criteria...

i.e.
create table #asdf (id int identity (1,1), name sysname, type varchar(10))
insert into #asdf
select name, type
from sysobjects
order by type, name

select (select min(id) from #asdf where type = a.type)
+ (select count(*) from #asdf where type = a.type and name < a.name)
- (select count(*) from #asdf where type < a.type), name, type
from #asdf a
order by name, type

not a great solution, and definitely not tested against a large data-set, but maybe it will give you or someone else a better idea.

ffoiii

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-22 : 08:28:27

this should help. play with it.

select autonumber = count(*), t1.*
from FName t1, FName t2
where
t1.Type + t1.Date + t1.SomeData1 + t1.SomeData2 + t1.SomeData2 > t2.Type + t2.Date + t2.SomeData1 + t2.SomeData2 + t2.SomeData2
group by t1.Type, t1.Date, t1.SomeData1, t1.SomeData2, t1.SomeData2
order by 1

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources