display top ten record

Oracle Forms blocks allow you to specify how many records to display, and they allow you to supply an "order by" clause for the default query in each database block.

Does the combination of these two built-in features of Oracle Forms not give you what you are asking for?

If you are referring to equivalent functionality of SQL setver's
SELECT TOP (1000) .... syntax, strictly speaking there isn't one in Oracle.

However you can use rownum in the where clause.

Select *
From mytable
Where rownum <1001.

Like SQL server's TOP, sort operations are performed on the result data set, not on the whole table.

The value in this technique is generally for viewing what the data looks like without selecting the whole table, rather that actually processing those "top" records.

Regards,
JT

You can also create one specific view for such purpose.

I'm not sure this is true in Oracle: "sort operations are performed on the result data set, not on the whole table."  My understanding is that if you use rownum in an Oracle query, for example: "rownum < 11" Oracle applies the rownum limit *BEFORE* doing any sorting, not after.  Usually in Oracle, if you want a combination of sorting an rownum, you need to use a multi-level query something like this:

select * from 
    (select * from my_table
    order by some_value desc) 
where rownum < 11;

Select allOpen in new window

But, the disadvantage of this approach is the performance penalty, since Oracle must read, and sort, all of the rows before it can determine which ones are the "top 10".  That is, unless you are sorting by an indexed column value.  In that case, Oracle can use the index efficiently to find the values in the order you want.

You've just explained what I was saying. The reason you needed to encapsulate the order by as you did, is precisely because if you don't, the sort is only applied after you have the result set limited by rownum.

SQL server's Select TOP (10) for example works in a similar manner. It sorts on the limited result set, not the whole table.

Top 10 records, there are many ways. First you have to identify that top according to which column and then order by. Suppose top 10 with respect to id.
With N1 As (Select * from Employee order by id desc)
Select * from N1 where rownum < 11

Vishal... What you have proposed differs from the equivalent functionality in SQL server (of the TOP n rows syntax).

Yours of course will work in both instances, but what is important here is the requirement. If the asker wants the TOP 10, as a SQL Server equivalent, it is simply SELECT * from table where rownum <11.
You've added a layer of solution by ordering the data first, then selecting the ten rows as indicated by the pseudo-column rownum.

Only the asker can say which they're truly aiming for... it could be either.