Sequential Record Numbers - SQL Server Forums

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)

Sequential Record Numbers

Author

Topic

atulkukreja
Starting Member

13 Posts

Posted - 2004-08-18 : 10:38:21

How can I add a sequential number with a result set?

Example: A table named Fruits has a single column Fruit char(10).
There are 3 rows with Apples, Oranges and Bananas as the values.

I want the result set to be:

Rowno Fruit
1 Apples
2 Oranges
3 Bananas

Thanks.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-08-18 : 10:46:40

[code]select distinct identity(int,1,1) RowNo, Fruit
into #Fruits
from Fruits

select * from #Fruits

drop table #fruits
[/code]

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-18 : 10:58:29

or in one select statement:

select rank=count(*), t1.Fruit
from Fruit t1, Fruit t2
where t1.Fruit >= t2.Fruit
group by t1.Fruit
order by 1

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

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-08-18 : 11:04:49

quote:
Originally posted by spirit1select rank=count(*), t1.Fruit
from Fruit t1, Fruit t2
where t1.Fruit >= t2.Fruit
group by t1.Fruit
order by 1

This does not appear to generate a sequential number??

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-18 : 11:11:52

try this:

declare @Fruit table (Fruit varchar(20))
insert into @Fruit
select 'Apples' union all
select 'Oranges' union all
select 'Pears' union all
select 'Grapes' union all
select 'Strawberries' union all
select 'Bananas'

select rank=count(*), t1.Fruit
from @Fruit t1, @Fruit t2
where t1.Fruit >= t2.Fruit
group by t1.Fruit
order by 1

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

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-08-18 : 11:20:38

Gotcha, my data set did not assume that fruit was unique. But after re-reading the post. It seems that this is a safe assumpotion.

Nice work spirit1

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-18 : 11:25:55

it's stuff like this you learn here, that's all :)))
i wonder why no one reads the articles here. it's all there... :))

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

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-08-18 : 11:31:14

I agree, though there would be little need, or use, for the forums if everyone did.

:)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-18 : 11:44:31

nah. forum are always useful. because people don't always understand what they read. :)))

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

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2004-08-18 : 15:13:15

And speaking of forums ... I'm moving this post to a different one. We're in the T-SQL forum now.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources