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 |
|
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 test123select 23,'testt' union allselect 234,'testt' union allselect 13,'tett' union allselect 253,'te9stt' union allselect 632,'te4tt' union allselect 53,'tes2t' union allselect 643,'te6tt' union allselect 725,'t7tt' union allselect 742,'t9stt' selectmax(y.id) as third_row_idfrom(select top 5id, text_1from test123order by id asc) as y |
|
|
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 rowidfrom( select row_number() over (order by <your_field_for_sort>) as nn, rowid from <your_table_name>) _tablewhere nn=10Best regards,Devart Team |
|
|
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:-- 2005SELECT %%LockRes%%, *FROM MyTable-- 2008SELECT %%physloc%%, *FROM MyTable |
|
|
ROLASHISH
Starting Member
3 Posts |
Posted - 2012-01-13 : 01:06:00
|
-- 2008SELECT %%physloc%%, *FROM MyTableIf i use this command how i use like command rownum > 5.ashishn |
|
|
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 ShawSQL Server MVP |
|
|
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 waySELECT TOP 1 *FROM ( SELECT TOP 10 * FROM <theTable> ORDER BY <SomeCondition> ASC / DESC ) AS dtORDER BY <SomeCondition> DESC / ASC Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
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.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
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.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor 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 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|