SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to get rowid
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqldoubt
Starting Member

17 Posts

Posted - 07/06/2010 :  19:16:57  Show Profile  Reply with Quote
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

USA
37143 Posts

Posted - 07/06/2010 :  19:58:03  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Switzerland
746 Posts

Posted - 07/07/2010 :  01:39:39  Show Profile  Reply with Quote
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 - 07/07/2010 :  02:00:46  Show Profile  Visit Devart's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 07/07/2010 :  11:23:20  Show Profile  Reply with Quote
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

India
3 Posts

Posted - 01/13/2012 :  01:06:00  Show Profile  Reply with Quote
-- 2008
SELECT %%physloc%%, *
FROM MyTable

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

ashishn
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 01/13/2012 :  04:57:44  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 01/13/2012 :  05:29:28  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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

Edited by - Transact Charlie on 01/13/2012 05:36:26
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 01/13/2012 :  07:56:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 01/13/2012 :  09:26:09  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000