Equivalent Oracle Rownum Pseudo Column in SQL Server

[Edd01]

I am having trouble finding an equivalent SQL Server statement for the following Oracle Statement:

SELECT COL1, COL2 FROM MYTABLE WHERE ROWNUM < 10

The ROWNUM Psuedo column in Oracle would output the Row number of each row in the query and hence only the top 10 rows would be shown for this query.

I am using SQL Server 6.5
I have tried using the SELECT TOP n statement but it appears not to be supported in my version.

Is there an Equivalent to the ROWNUM column or do the later versions of SQL Server support TOP?

Thanks

Edwin Derrick

 

[Zathras]

ROWNUM is an Oracle thing. The ANSI standard for SQL is that rows in a table have no intrinsic order.

SELECT TOP n should work in 6.5:

SELECT TOP 10 COL1, COL2 FROM MYTABLE

 

[tlbroadbent]

TOP was not implemented in SQL Server until version 7. You can use SET ROWCOUNT in SQL 6.5. The following will restrict the number of rows to 10.

/* Set the rowcount to 10*/
SET ROWCOUNT 10

Select * From Table

/* Reset the rowcount to unlimited*/
SET RWOCOUNT 0 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.