Only select a certain number of results?

[irate]

I have a SELECT statement that, for example, returns 50 out of 100 rows in a table. Is there a way to only select the first five of those 50 results, maybe using the count function or something?
I was going to use asp to count through the recordset to five and then stop, but the means the sql is still getting 50 results, i think if it only gets five in the first place it will be quicker for the asp to just get them rather than itterating through the whole record set only to get the first five...

 

[ruupy]

HeyHey,

Try using this one:

select top 50 * from [tablename]
order by [whatever column]

If u want to select a part of the recordset, try putting the primairy result in a temp-table and then use the 'top xx' clause again....

-keep it short 'n simple-


Succes,
RuupY

 

[rajeevnandanmishra]

Hi there,
If you are using SQL 6.5 or below you can use :
SET ROWCOUNT 5
SELECT ..... myQyery
SET ROWCOUNT 0


And If you are using SQL 7.0 or above it is relatively simple:
SELECT TOP 5 .... myQyery

Remember if you are using TOP clause you have give and ORDER BY clause also.

 

[Bernadette]

Hi There

What you need to use is Top N.
This will select the top 5 records from my table.

Select * Top 5 from MyTable


Or you can use the Set Rowcount 5.

Remember however to Set Rowcount 0 so that all rows will be returned the next time.

Hope This Helps :->



Bernadette

 

[irate]

Wow, thanks guys...
Pretty simple too.
I like it.