How to query the top 10 records in Oracle

According to the time condition, the first 10 and the last 10 are selected.

1. There is a time field, which is sorted according to the time condition, and the first ten and last ten are taken.

1) The first ten:

select * from (select * from tab_name a order by

date_col )

where rownum<11;

2) The last ten:

select * from (select * from tab_name a order by date_col desc)

where rownum<11;

2. There is no time field, and the first ten and last ten are taken directly according to the physical storage order.

1) The first ten:

select * from (select * from tab_name a order by rownum)

where rownum<11;

2) The last ten:

select * from (select * from tab_name a order by rownuml desc)

where rownum<11;

The difference between the two is that the grammar is different.

The top or row number can be used to select the first 10 entries in SQLSERVER, but only the row number can be used in Oracle.

As shown in the data:

Now it is required to take out the top 10 digits in reverse order of ID:

Oracle:

select , from

select test.*,row_number() over (order by id desc) rn from test) t

where rn<=10;

Result: Execute method in sqlserver:

select top 10 * from test order by id desc;

Result: Note: SQL Server 2005 and later versions can also be executed using statements in Oracle, and the results obtained are exactly the same.

As mentioned above, when oracle and sqlserver query the first n data, oracle does not have the top keyword, and uses the keyword rownum to obtain the data of the first n data.

There is no difference, just that the syntax of the two queries is a little different.

The statement upstairs about oracle is a bit problematic and should be better with subqueries.