RowCount & order by clause issue

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 2000 Forums

SQL Server Development (2000)

RowCount & order by clause issue

Author

Topic

m18s
Starting Member

2 Posts

Posted - 2007-12-19 : 12:52:27

Hi,

I have one temporary table in which i am inserting data base on the code below.
insert into #temptable
Select column1, column2, column3 from table order by column3 desc

I need to retrive rows from this temp table based on some value in variable.

for this I set rowcount property

Set RowCount = "variable value"
Select * from #temptable where column2 = "SomeValue"

I want this query should return number of rows (specified in variable) which have column2 = "Somevalue" and greater value of column 3.

Is it possible, the resultset this query is returning may have rows with lesser value of column3?

or should I use
Set RowCount = "variable value"
Select * from #temptable where column2 = "SomeValue" order by column3 desc.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 12:56:15

Select * from #temptable where column2 = 'SomeValue' and column3 > 'SomeValue'
order by column3 desc

E 12°55'05.25"
N 56°04'39.16"

m18s
Starting Member

2 Posts

Posted - 2007-12-19 : 13:07:54

thanks for the reply, i want to know whether the code i have mentioned above may return randon value of column3
let say i have value

Column1 Column2 Column3
1 2 107
2 3 106
3 2 105
4 2 104
5 3 103
6 3 102
7 2 101

Set RowCount 2
Select column3 from #temptable where column2 = 2

This query will return 107,105
or it may return any value of column 3 where column2 = 2 (107,105,104,101)

hope i am clear now

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 13:15:06

Noone can tell. The physical order of records are not important.
You should always use ORDER BY to get same result between executions.

You can however user ORDER BY NEWID() to get random records.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 13:15:51

[code]SET ROWCOUNT 2

SELECT Column3
FROM #Temp
WHERE Column2 = 2
ORDER BY NEWID()[/code]

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-19 : 13:23:37

To get 107,105 you need to use

SET ROWCOUNT 2

SELECT Column3
FROM #Temp
WHERE Column2 = 2
ORDER BY Column3 DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources