|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
TOP Operator - Variable?I am wanting to select a particular number of rows using the TOP operator. The number of rows in particular are provided by a varable if possible. SELECT TOP @oqs NEWID() AS ID,asmt_v2_question_id, qtext, qindex, qtype, answer_url FROM asmt_v2_questions Obviously this doesn't work. Any thought on how to acheive this? Cheers, Adam Use SET ROWCOUNT instead of TOP.
Show quote "Adam Knight" <a***@pertrain.com.au> wrote in message news:Olc6JTXxFHA.1284@tk2msftngp13.phx.gbl... > Hi all, > > I am wanting to select a particular number of rows using the TOP operator. > > The number of rows in particular are provided by a varable if possible. > > SELECT > TOP @oqs NEWID() AS ID,asmt_v2_question_id, qtext, qindex, qtype, > answer_url > > FROM > > asmt_v2_questions > > Obviously this doesn't work. > > Any thought on how to acheive this? > > Cheers, > > Adam > > The select is a part of a UNION clause.
IE: Select * FROM mytable UNION ALL Select * From myTable2 How Would i go about only limiting the number of rows in the second query using SET ROWCOUNT ? use a subquery:
SET ROWCOUNT @var SELECT * FROM (SELECT * FROM myTable UNION ALL SELECT * FROM myTable2) u ORDER BY someColumn Show quote "Adam Knight" <a***@pertrain.com.au> wrote in message news:OHftmdXxFHA.3860@TK2MSFTNGP09.phx.gbl... > The select is a part of a UNION clause. > IE: > Select * FROM mytable > UNION ALL Select * From myTable2 > > How Would i go about only limiting the number of rows in the second query > using SET ROWCOUNT ? > Wouldn't the set row count limit the results of both queries in the UNION
ALL ??? The first query need to return everything that matches its criteria!!! Only a specified number of rows are to be returned matching the second part of the union though ??? Show quote "Brian Selzer" <br***@selzer-software.com> wrote in message news:uVUsJrXxFHA.2792@tk2msftngp13.phx.gbl... > use a subquery: > > SET ROWCOUNT @var > SELECT * FROM (SELECT * FROM myTable UNION ALL SELECT * FROM myTable2) u > ORDER BY someColumn > > "Adam Knight" <a***@pertrain.com.au> wrote in message > news:OHftmdXxFHA.3860@TK2MSFTNGP09.phx.gbl... >> The select is a part of a UNION clause. >> IE: >> Select * FROM mytable >> UNION ALL Select * From myTable2 >> >> How Would i go about only limiting the number of rows in the second query >> using SET ROWCOUNT ? >> > > I guess you're stuck with Dynamic SQL!
Show quote "Adam Knight" <a***@pertrain.com.au> wrote in message news:eNmWPq7xFHA.3772@TK2MSFTNGP10.phx.gbl... > Wouldn't the set row count limit the results of both queries in the UNION > ALL ??? > > The first query need to return everything that matches its criteria!!! > Only a specified number of rows are to be returned matching the second > part of the union though ??? > > "Brian Selzer" <br***@selzer-software.com> wrote in message > news:uVUsJrXxFHA.2792@tk2msftngp13.phx.gbl... >> use a subquery: >> >> SET ROWCOUNT @var >> SELECT * FROM (SELECT * FROM myTable UNION ALL SELECT * FROM myTable2) u >> ORDER BY someColumn >> >> "Adam Knight" <a***@pertrain.com.au> wrote in message >> news:OHftmdXxFHA.3860@TK2MSFTNGP09.phx.gbl... >>> The select is a part of a UNION clause. >>> IE: >>> Select * FROM mytable >>> UNION ALL Select * From myTable2 >>> >>> How Would i go about only limiting the number of rows in the second >>> query using SET ROWCOUNT ? >>> >> >> > > Brian.
Thankyou very much for your responses. I have found the willingness of people to provide advice and inight on this newsgroup quite exceptional. In the end it appears i got around using Dynamic Sql by doing as follows: 1) Create a Temporary Table 2) Set up a parameterised Set RowCount attribute (applies to temporary table) 3) Populate the temporary table with the data returned from the second query in the original union. 4) Modify the second query in the original union to select everything from the temporary table. Works a treat... Cheers, Adam rian Selzer" <br***@selzer-software.com> wrote in message Show quote news:%23AdGZ6EyFHA.464@TK2MSFTNGP15.phx.gbl... >I guess you're stuck with Dynamic SQL! > > "Adam Knight" <a***@pertrain.com.au> wrote in message > news:eNmWPq7xFHA.3772@TK2MSFTNGP10.phx.gbl... >> Wouldn't the set row count limit the results of both queries in the UNION >> ALL ??? >> >> The first query need to return everything that matches its criteria!!! >> Only a specified number of rows are to be returned matching the second >> part of the union though ??? >> >> "Brian Selzer" <br***@selzer-software.com> wrote in message >> news:uVUsJrXxFHA.2792@tk2msftngp13.phx.gbl... >>> use a subquery: >>> >>> SET ROWCOUNT @var >>> SELECT * FROM (SELECT * FROM myTable UNION ALL SELECT * FROM myTable2) u >>> ORDER BY someColumn >>> >>> "Adam Knight" <a***@pertrain.com.au> wrote in message >>> news:OHftmdXxFHA.3860@TK2MSFTNGP09.phx.gbl... >>>> The select is a part of a UNION clause. >>>> IE: >>>> Select * FROM mytable >>>> UNION ALL Select * From myTable2 >>>> >>>> How Would i go about only limiting the number of rows in the second >>>> query using SET ROWCOUNT ? >>>> >>> >>> >> >> > > U can try like this
declare @d int, @sqlquery nvarchar(100) select @d = 10 select @sqlquery = 'select top ' + cast(@d as varchar) + 'au_id from authors' exec sp_executesql @sqlquery Praveen. try this
DECLARE @VAR int DECLARE @var2 varchar(100) SELECT @VAR = 100 SELECT @var2 = 'SELECT TOP ' + CAST(@VAR AS varchar(3)) + ' * FROM ,tablename>' exec(@var2) or use select @sqlquery = N'select top ' + cast(@d as varchar) + 'au_id from if you want to use praveen's Regards R.D Show quote "Praveen" wrote: > U can try like this > > declare @d int, > @sqlquery nvarchar(100) > > select @d = 10 > > select @sqlquery = 'select top ' + cast(@d as varchar) + 'au_id from > authors' > > exec sp_executesql @sqlquery > > Praveen. > > SQL 2005 will do
-- Show quoteDandy Weyn [MCSE-MCSA-MCDBA-MCDST-MCT] http://www.dandyman.net Check my SQL Server Resource Pages at http://www.dandyman.net/sql "Adam Knight" <a***@pertrain.com.au> wrote in message news:Olc6JTXxFHA.1284@tk2msftngp13.phx.gbl... > Hi all, > > I am wanting to select a particular number of rows using the TOP operator. > > The number of rows in particular are provided by a varable if possible. > > SELECT > TOP @oqs NEWID() AS ID,asmt_v2_question_id, qtext, qindex, qtype, > answer_url > > FROM > > asmt_v2_questions > > Obviously this doesn't work. > > Any thought on how to acheive this? > > Cheers, > > Adam > >  |
|||||||||||||||||||||||
Other interesting topics