how to select a paticular record from a table

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)

how to select a paticular record from a table

Author

Topic

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2009-07-17 : 06:09:29

imagine there is a table with 540 records. i wanna select for example the 46th record. how can i do this?

****<>****

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-07-17 : 06:12:52

What do you mean by the 46th record? How is the data sorted? Do you have a clustered index? If so, what type of dat is the index, if not, how would you know you had the right record?

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-07-17 : 06:21:09

You can refer this

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

but how you know which is 46th record? whats the order criteria?

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-17 : 06:28:46

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2009-07-17 : 08:27:16

the Select top 46.... statment returns 46 records. but i want the 46th record only. there is no condition for select. the number 46 is an example number that i check it before to be less than the count of records

****<>****

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-07-17 : 08:36:03

quote:
Originally posted by mahdi87_gh

the Select top 46.... statment returns 46 records. but i want the 46th record only. there is no condition for select. the number 46 is an example number that i check it before to be less than the count of records

****<>****

quote:
Originally posted by RickD

What do you mean by the 46th record? How is the data sorted? Do you have a clustered index? If so, what type of dat is the index, if not, how would you know you had the right record?

How will you know it is the <n>th record? There is no set way of storing the data unless you have a clustered index, and even then, there is no sure way of guaranteeing that way the <n>th record inserted.

asgast
Posting Yak Master

149 Posts

Posted - 2009-07-17 : 08:46:46

select into a temp table with an identity field in it
select from temp table where identity = 46

use sub select if you don't like temp tables

user order by newid() if you are trying to create a random selection

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-07-17 : 10:13:38

Is there an identity column on the table in question.

Also guys what's with the over complicated answers, yet no one has even ask this question, what do you need to know about indexes for when if theres an identity column is a simple where in a select.

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-07-17 : 10:17:24

infact what wrong with this for an answer

SELECT IDENTITY(INT)AS IDCOL,[TableName].* INTO #Temp FROM [TableName]

SELECT * FROM #Temp WHERE IDCOL = 46

mahdi87_gh
Yak Posting Veteran

72 Posts

Posted - 2009-07-17 : 11:24:24

thanks my friend NeilG

****<>****

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-07-17 : 11:39:24

quote:
Originally posted by NeilG

infact what wrong with this for an answer

SELECT IDENTITY(INT)AS IDCOL,[TableName].* INTO #Temp FROM [TableName]

SELECT * FROM #Temp WHERE IDCOL = 46

How can you guarantee that was the 46th row inserted into the table?

The original question was how to find the <n>th row in a table, without a sort order or index, this is not a straight forward question or answer.

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-07-19 : 18:26:39

quote:
Originally posted by NeilG

Is there an identity column on the table in question.

Also guys what's with the over complicated answers, yet no one has even ask this question, what do you need to know about indexes for when if theres an identity column is a simple where in a select.

What's with the over simplification of the problem, Neil? Show us where the OP said anything about an IDENTITY column.

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-07-19 : 18:27:30

quote:
Originally posted by mahdi87_gh

thanks my friend NeilG

****<>****

Your friend has not provided a correct answer unless you actually do have an IDENTITY column which you did not state. ;-)

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources