SQL takes the first few lines of a record statement

SQLite database:

The code is as follows	Copy Code
SELECT * FROM table limit N

DB2 Database:

The code is as follows	Copy Code
SELECT * FROM tab fetch the only

Oracle Database:

The code is as follows	Copy Code
SELECT * from tab where RowNum <=10

SQL Server database:

The code is as follows	Copy Code
Select Top * from tab

1 if there is a primary key in the table, you can use in the way.

The code is as follows	Copy Code
SELECT * from Tmp_trans t WHERE ID in ( SELECT Top 2 ID From Tmp_trans WHERE Po_no=t.po_no ORDER BY Trans_date DESC )

2 If there is no primary key, you can use to determine how many records before this record. However, if you encounter trans_date the same situation will not be allowed when using this method. If there are three of the largest records in the Trans_date, these three records will be found.

The code is as follows	Copy Code
SELECT * From Tmp_trans t WHERE ( SELECT COUNT (*) From Tmp_trans WHERE Po_no=t.po_no and Trans_date>t.trans_date ) <2

3) Use the CROSS APPLY clause. CROSS Apply is a new feature that comes out of SQL Server 2005 and is used to pass parameters when a table joins.

The code is as follows	Copy Code
SELECT DISTINCT b.* From Tmp_trans A CROSS APPLY ( SELECT Top (2) * from Tmp_trans WHERE a.po_no=po_no order by Trans_date DESC ) b

2. Use the automatically generated row number. You can group with the partition by clause when using row_number (). It is recommended that this approach be used.

The code is as follows	Copy Code
SELECT * FROM ( Select Row_number () over (PARTITION by po_no ORDER by Trans_date DESC) as rowid,* From Tmp_trans ) A where rowid<=2

MySQL database:

The code is as follows	Copy Code
SELECT * from tab limit 10

Note: If you have the following ORDER BY clause for Oracle queries, the query data may not be what you want
Oracle processing process is: First search out the rownum<2 information, and then sort

The code is as follows	Copy Code
Select table_name from T_tables where ROWNUM<2 ORDER BY table_name DESC;

It needs to be written in the following ways:

code is as follows	copy code
Select t2.* From (SELECT * to t_tables T ORDER by table_name DESC) T2 where rownum<2