SELECT TOP <variable> FROM ...

[smn198]

I am trying to get around SQL's inability to use TOP with a variable. I am trying to select the TOP X and order by NEWID() to get X random records in a table. Any ideas?!

 

[RhythmAddict112]

What kind of Database? TOP is supported in sql server, but not in Oracle..You can get around it in oracle by using ROWNUM...ie

SELECT Col1, Col2
FROM TABLE
WHERE ROWNUM < 100;

Use your resources, you're on the internet!

 

[mrdenny]

Dymanic SQL is going to be your answer.

declare @top int
declare @CMD varchar(1000)
set @Top = 5
set @CMD = 'select top ' + @Top + ' * from {table}'
exec @CMD

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[smn198]

Thanks for the replies. I neglected to mention the SQL is actually going into an application. All I can write are static queries and not Dynamic SQL as what I write is a string to be used as a subquery.

 

[mrdenny]

I'd recommend converting the entire query into a stored proc. If that isn't an option your pretty much out of luck, unless you can have the app generate the query for you.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[CzechNET]

As in ?
Variable = "SELECT TOP " & MY_COP & " FROM ....."
Eitehr way convert to stored proc ! It's going to be for the best