SQL Server - How to fetch first 10 rows from list of 100s

Asked By Gopinath G on 10-Oct-05 07:07 AM
hi friends,

    I want to fetch the first 10 rows from a table which consists of some 100+ rows.

    Can you suggest me a good example wtihout using temp table.

    thanks a lot.

    regards,
    gopinath g.

query to fetch top 10 row - Asked By sakthi kumaran on 10-Oct-05 07:34 AM

hi there,
this is sakthi, here is the query to fetch top 10 

select * from tbl_name where rownum <11;

This will fetch you first 10 rows of emp
rownum is the default attri of any table. similarly u haw rowid also.

in Sql Server, you can - Asked By Peter Bromberg on 10-Oct-05 07:36 AM

SET ROWCOUNT 10

declare #temptable

Select * from sourcetable into @temptable

Depends on what you mean - Asked By Aarthi Saravanakumar on 10-Oct-05 08:16 AM

by first 10 rows. There is a TOP statement in SQL Server 2000 that can be used
example : to find the top 10 rows by date

SELECT TOP 10 * FROM Table
Order By SomeDate asc
TOP command is best choice for this application - Asked By Michael Stewart on 10-Oct-05 07:11 PM
Using the TOP command in your query, as suggested by Aarthi, is the best solution; however, if you decide to use SET ROWCOUNT 10 at the beginning of your query, be sure to use SET ROWCOUNT 0 at the end of the query to reset this parameter.  If you forget to do this, the rowcount will be set to 10 during your entire session from that point forward.

Another tip when using the TOP command - Also use ORDER BY to make sure you get the appropriate rows to display for your query.

--SAMPLE 1--
SET ROWCOUNT 10

FROM TableA
WHERE Active = 'T'
ORDER BY CustID

SET ROWCOUNT 0

--Sample 2--
SELECT TOP 10 *
FROM TableA
WHERE Active = 'T'
ORDER BY CustID

Good luck

ms
Careful With SET ROWCOUNT - Asked By F Cali on 11-Oct-05 11:37 PM
Be careful in using SET ROWCOUNT as it affects all statements within the query.  It will have a significant effect when there's a sub-query within the main query because the sub-query will also only return the number of rows set by the ROWCOUNT.

For example,

SET ROWCOUNT 10

SELECT * FROM Table1
WHERE ID IN (SELECT ID FROM Table2)

In this example, it is not a guarantee that it will return 10 rows from Table1 (assuming that Table1 has more than 10 rows).  The sub-query from Table2 will also be affected by the ROWCOUNT, which therefore will only return 10 rows.  Those 10 IDs returned by Table2 might not exist at all in Table1.