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
 General SQL Server Forums
 New to SQL Server Programming
 how to get rowid

Author  Topic 

sqldoubt
Starting Member

17 Posts

Posted - 2010-07-06 : 19:16:57
I have select TOP 10 from order.
now i want to get that 10th record rowid .

can any one gimme the code for that.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-06 : 19:58:03
Is RowId a column with identity data type?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-07 : 01:39:39
try this:

create table test123
(id int
,text_1 varchar(10)
)

insert into test123
select 23,'testt' union all
select 234,'testt' union all
select 13,'tett' union all
select 253,'te9stt' union all
select 632,'te4tt' union all
select 53,'tes2t' union all
select 643,'te6tt' union all
select 725,'t7tt' union all
select 742,'t9stt'


select
max(y.id) as third_row_id
from
(
select top 5
id, text_1
from test123
order by id asc
) as y
Go to Top of Page

Devart
Posting Yak Master

102 Posts

Posted - 2010-07-07 : 02:00:46
Hello,

If you use MSSQL 2005 or MSSQL 2008, you can try this for example:

select
rowid
from
(
select
row_number() over (order by <your_field_for_sort>) as nn,
rowid
from <your_table_name>
) _table
where
nn=10

Best regards,

Devart Team
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-07-07 : 11:23:20
Are you talking about the Physical ID? Here are 2 samples for 2005 and 2008:
-- 2005
SELECT %%LockRes%%, *
FROM MyTable

-- 2008
SELECT %%physloc%%, *
FROM MyTable
Go to Top of Page

ROLASHISH
Starting Member

3 Posts

Posted - 2012-01-13 : 01:06:00
-- 2008
SELECT %%physloc%%, *
FROM MyTable

If i use this command how i use like command rownum > 5.

ashishn
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-13 : 04:57:44
You don't, that's not what it's for.

You say you want rownum 5, row number by what order? SQL doesn't have an order to rows in a table, so if you want the 10th record, you need to define what makes it the 10th row.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-13 : 05:29:28
you are being really vague. Post some sample data and required output.

Here's one way

SELECT TOP 1 *
FROM
(
SELECT TOP 10 *
FROM <theTable>
ORDER BY <SomeCondition> ASC / DESC
)
AS dt
ORDER BY
<SomeCondition> DESC / ASC


Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-01-13 : 07:56:21
Also, resurrecting a 2 year old thread is a no no

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-13 : 09:26:09
quote:
Originally posted by DonAtWork

Also, resurrecting a 2 year old thread is a no no

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp



Oops - didn't see that!

yeah,

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page
   

- Advertisement -