Selecting only 5 records out of a possible 100. 3

[skurpyun]

Here is what i want to do, I want to query a database that has say 100 records that match my query, but I only want to get the first 5 of those 100 record. Is there a way to accomplish this with an SQL statement?

Currently I am just getting all the records, and then using VBscript to loop through the recordset and pull out the first 5 rows of info that i want, then discarding the recordset. This i imagine is not the most efficient method.

any thoughts?

thanks in advance.

 

[tlbroadbent]

Use Top to limit the rows returned.

Select Top 5 <column list>
From YourTable
Where <your criteria>
Order By <column list> Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[swampBoogie]

You have to define what you mean by first. Records in a table does not have any order. For instance if you want records with the 5 most recent dates you could do

select top 5 * from t order by dateColumn desc

top 5 restricts the number of rows return with respect to the order by clause.

 

[skurpyun]

so following both your examples, I am assuming it's perfectly ok to say

Select top 5 column1, column2, column3 from tablename order by columndate

And that should give me the data in column 1, 2 and 3 for the first 5.

Cool. i will play around with it.
so simple.
thanks to you both.

 

[SQLBill]

I am assuming it's perfectly ok to say

Select top 5 column1, column2, column3 from tablename order by columndate

And that should give me the data in column 1, 2 and 3 for the first 5.

Yes, it is okay.
Yes, it will give you the data, but for the first 5 BASED ON COLUMNDATE (order by columndate).

Just wanted to make sure you are understanding what the TOP 5 is based on.

-SQLBill

 

[skurpyun]

Got it.

Thanks SQLBill, swampBoogie, and tlbroadbent for all your inputs.