How to get a Number of a Row - 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

SQL Server Development (2000)

How to get a Number of a Row

Author

Topic

CLages
Posting Yak Master

116 Posts

Posted - 2005-11-19 : 06:29:29

Hi, i have been searching but i find nothing.

i have one table

cod name
123 aaaa
124 bbbb
125 xxxx
etc

when in QA i get using select

cod name
1 123 aaaa
2 124 bbbb
3 125 xxxx

How can i get the Row Number ?

i would like to get 2 if i Select * from table where cod = 124

tks
Clages

By the way in this case i can not use Identity column

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-19 : 06:49:53

select * from
(select identity(int, 1, 1) as rownum, *
from yourTable) t
where cod = 124

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

CLages
Posting Yak Master

116 Posts

Posted - 2005-11-19 : 07:36:11

SORRY , I GET THIS ERROR

Incorrect syntax near the keyword 'identity'

AS I SAID I HAVE NO IDENTITY COLUMN IS THIS TABLE

TKS
cLAGES

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-19 : 07:41:33

Select rowNum from
	(
	select (select count(*) from yourTable where cod<=T.cod) as RowNum, cod from yourTable T
	) T
where cod=124

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-19 : 07:46:09

quote:
Originally posted by spirit1

select * from
(select identity(int, 1, 1) as rownum, *
from yourTable) t
where cod = 124

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

Select identity(int,1,1) from ..... wont work

I think you meant this

Select identity(int,1,1) as rowNum, cod into #t from yourTable
Select rowNum from #t where cod=124
Drop table #t

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-19 : 07:49:33

yeah... next time remind me not to answer questions after sleeping only 4 hours...

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-19 : 08:14:46

quote:
Originally posted by spirit1

yeah... next time remind me not to answer questions after sleeping only 4 hours...

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

So you are very busy?

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-19 : 10:20:21

no not really... it was friday night out... went to sleep at 6 am

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

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-11-21 : 06:52:35

How about reminding you not to answer questions that have no meaning? You want row numbers, use Excel.

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-21 : 06:59:35

or

Use an identity column

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-21 : 07:55:11

sure Don... that would work too...

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

CLages
Posting Yak Master

116 Posts

Posted - 2005-11-21 : 08:44:17

When i Asked how to get a row number, of couse i have a reason to use
this.
The reasom is to create a Specific table getting values of several tables, But this new table can not have identity column, because the column that will get this values is Char(10).

In other words
the New table has this columns

Code(char(10) LoteNumber(char(10) (both are key)

loteNumber should be a combination of Columns (string Of course)
but in same case I have Duplicate Key, then i think , if
add the RowNumber is this combination of columns , i will never get
Duplicate Key.

tks
Anyway

Clages

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-21 : 09:11:25

will this help you?
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx
point 1. first -

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