Record Number - 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 2005 Forums

Transact-SQL (2005)

Record Number

Author

Topic

glendcruz
Yak Posting Veteran

60 Posts

Posted - 2010-09-09 : 21:08:03

Hi Everyone,
I need to find out the record number in a table

Eg i have a table

Table x
Name
John -- this is the first record
sonia -- the second record
--
--
etc

I just want the record number

I am running into a brick wall, can any one help please

Thanks in advance

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-09-09 : 23:09:50

There is no such thing like Record Number. But if you want a sequential number of rows you can use ROW_NUMBER.


SELECT ROW_NUMBER() OVER(ORDER BY Name) AS RecordNumber
  FROM x

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-09-10 : 12:32:13

It would be better to have a proper table structure, but maybe you can make use of a physical row mapping?

-- 2005
SELECT %%LockRes%%, *
FROM MyTable

-- 2008
SELECT %%physloc%%, *
FROM MyTable

glendcruz
Yak Posting Veteran

60 Posts

Posted - 2010-09-11 : 01:44:54

This example written by malpashaa

SELECT ROW_NUMBER() OVER(ORDER BY Name) AS RecordNumber
FROM x

This exampole written by lamprey

SELECT %%LockRes%%
FROM MyTable

This too gives a number of characters which does not make sense.

I will explain the problem once again

SELECT * FROM S_EMP
WHERE DEPT_ID = 31

The result set will be

id, Lantname, first_name,,,,,, dept-id
231 Jackson john ---- 31
47 Glen Dcruz ---- 31

Now jackson could be on row number 25 on the table and Glen could be on rownumber 55 on the table. How do i get the right row number .

Let me know if you have any ideas
Thanks once again for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources