SELECT TOP problem

1 Jul 2005 11:16 AM

B.J.

Hi,

I want to do select top n rows but n is variable :

SELECT TOP @MyVar ID FROM Table;

Because I get @MyVar from

SELECT @MyVar=COUNT(*) FROM Table WHERE ProfileID=@ProfileID;

which is not constant. But this does not work. Any suggestions, please.

Thank you.

1 Jul 2005 11:21 AM

Jens Süßmeyer

http://www.sqlteam.com/item.asp?ItemID=233

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

Show quote

"B.J." <B*@discussions.microsoft.com> schrieb im Newsbeitrag
news:1186C1D5-DEFF-4E12-82C2-6497441DAA6A@microsoft.com...
> Hi,
>
> I want to do select top n rows but n is variable :
>
> SELECT TOP @MyVar ID FROM Table;
>
> Because I get @MyVar from
>
> SELECT @MyVar=COUNT(*) FROM Table WHERE ProfileID=@ProfileID;
>
> which is not constant. But this does not work. Any suggestions, please.
>
> Thank you.

1 Jul 2005 11:40 AM

David Portas

SELECT id
FROM Table
WHERE profileid = @profileid ;

Since you haven't specified ORDER BY the selection of rows in your TOP
query is undefined, therefore the above query gives the result you
asked for.

--
David Portas
SQL Server MVP
--