Re: does sqlplus support :"select top 10 * from table1" syntax like MS SQL?

"mlke" <ml_ke_at_hotmail.com> wrote in message news:df2c70d1.0403021216.3af4acc6_at_posting.google.com...
> I would like to select the top 10 record from a table? How can I do it?
> In MS SQL, it's easy using select top 10 from table1. But in oracle
 database,
> I can't use top, anyone have any suggestions? Thanks!

I do not know SQL server, but I think that you want to do what is called Top 'N' Analysis. In Oracle you can do this through an inline view. I found the explanation inserted at the bottom of this post on http://www.orafaq.com/msgboard/serverutil/messages/7285.htm :

Best regards,

Jacob Grydholt Jensen

Posted by Barbara Boehmer (66.120.226.1) on April 05, 2003 at 02:13:35:

In Reply to: Is there any command similar to SQLSERVER TOP command in Oracle posted by skrosuri on April 04, 2003 at 16:35:18:

In Oracle, top-n analysis is accomplished by first ordering the values in an inner sub-query, then selecting the rows from an outer query. You can use DESC in the order by clause to get the last row, rather than the first row. Oracle does not guarantee the row returned until or unless an order by clause has been applied.

SELECT * FROM (SELECT column_names

        FROM table_name

        ORDER BY column_name DESC)

WHERE ROWNUM <= 1

/ Received on Tue Mar 02 2004 - 22:04:35 CET