How to select row in table - 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 2008 Forums

Transact-SQL (2008)

How to select row in table

Author

Topic

Jerremy09
Starting Member

4 Posts

Posted - 2011-11-05 : 11:57:43

Hi,
I would like to know how to determine a single row in one column table. For example:

Create table #Temp (Radek int)
Insert into #Temp values ('667')
Insert into #Temp values ('34')
Insert into #Temp values ('589')
Insert into #Temp values ('456')
Insert into #Temp values ('567')
So I have table called #Temp of values 667, 34, 589, 456, 567. Table have 5 rows.

Now I would like to select value of each row and have some counting with it. I prepared statement

Determine @Citac int
Set @Citac = 1

While (@Citac< 6)
Begin
... Directli here I need to create some statement which let me select every row in table #Temp according to value of @Citac(Somethig like this: If value of @Citac is 1 then select first row of table #Temp, ....)....
Set @Citac = @Citac + 1
End

Could you please hepl me. Thank you.

vikki.seth
Yak Posting Veteran

66 Posts

Posted - 2011-11-05 : 12:33:26

you need to have another column such as ID identity column in the temp table and compare id with @citac.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-05 : 13:14:18

[code]
select *
from
(
select row_number() over(order by Radek) AS rn,*
from table
)t
where Rn = @Citac
[/code]
if you want order to be determined based on order of values

if you just want a random row then you need below

[code]
select *
from
(
select row_number() over(order by newid()) AS rn,*
from table
)t
where Rn = @Citac
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Jerremy09
Starting Member

4 Posts

Posted - 2011-11-07 : 01:16:05

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 04:27:24

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lewie
Starting Member

42 Posts

Posted - 2011-11-07 : 08:49:16

I suggest you put

select * into TABLEA
from
(
select row_number() over(order by newid()) AS rn,*
from table
)t

Before the WHILE and then use temp table TABLEA inside the while otherwise your performance may suffer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 09:16:55

quote:
Originally posted by Lewie

I suggest you put

select * into TABLEA
from
(
select row_number() over(order by newid()) AS rn,*
from table
)t

Before the WHILE and then use temp table TABLEA inside the while otherwise your performance may suffer

WHILE loop itself will be a performance hit

better t use set based technique

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-11-07 : 09:51:13

select row_number() over(order by newid()) AS rn,* into #temptablename
from table

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources