Statements for the database to take the first few records

Statement 1. ORACLEwww.2cto. comSELECT * statements. INFORMIXSELECTFIRSTN * statements. DB2SELECT * ROW_NUMBER () OVER (ORDERBYCOL1DESC) ASROWNUMWHEREROWNUMN or SELECTCOLUMNFROMT

The statement for the database to take the first few records 1. ORACLE www.2cto.com SELECT * FROM TABLE1 where rownum = N 2. informix select first n * FROM TABLE1 where 1 = 1 3. DB2 SELECT * ROW_NUMBER () OVER (order by COL1 DESC) as rownum where rownum = N or SELECT COLUMN FROM T

Statements for the database to take the first few records

1. ORACLE www.2cto.com

SELECT * FROM TABLE1 where rownum <= N

2. INFORMIX

Select first n * FROM TABLE1 where 1 = 1

3. DB2

SELECT * ROW_NUMBER () OVER (order by COL1 DESC) as rownum where rownum <= N

Or

Select column from table where 1 = 1 FETCH FIRST N ROWS ONLY

4. SQL SERVER

Select top n * FROM TABLE1 where 1 = 1

Or

Set rowcount n select * FROM TABLE1 where 1 = 1 set rowcount N1

5. SYBASE

Set rowcount n select * FROM TABLE1 where 1 = 1 set rowcount N1

6. MYSQL

SELECT * FROM TABLE1 where 1 = 1 LIMIT N

7. FOXPRO

SELECT * TOP N FROM TABLE ORDER BY COLUMN

8. ACCESS

Select top n * FROM TABLE1 where 1 = 1

1. Oracle Database

　　

SELECT * from tablename where rownum <= N

　　

2. Infomix Database

　　

Select first n * FROM TABLENAME

　　

3. DB2 database

　　

SELECT *

FROM (SELECT * ROW_NUMBER () OVER ({order by COL1 DESC}) as rownum from tablename)

Where rownum <= N

Or

SELECT COLUMN FROM TABLENAME FETCH FIRST N ROWS ONLY

　　

4. SQL Server database

　　

Select top n * FROM TABLENAME

　　

5. Sybase Database

　　

SET ROWCOUNT N

GO

SELECT * FROM TABLENAME

　　

6. MySQL database

　　

SELECT * FROM TABLENAME LIMIT N

　　

7. FoxPro Database

　　

SELECT * TOP N FROM TABLENAME ORDER BY COLUMN

　　

Top keywords:

1. First reference an example of a netizen error, SQL = "select top 30 * from data where title = '" & title1 & "'order by id desc"

Cause of Error Analysis: when both the where and top statements exist in the SQL statement, and the where condition column is not an appropriate index, the program executes a full table scan, the first step is to find records that meet the where condition, and the top limit here is also a false one. If the entire table has more than one million data tables, such a simple judgment may drag the database.

2. The solution is to remove the where filter statement. If you want to use the selcet top statement with the where condition, the column in the condition must be an appropriate index, for example, clustering indexes and primary columns in composite indexes. At the same time, you should avoid using functions, or statements that may cause all scans if NULL is used in the where condition. Keep this in mind. Otherwise, a full table scan will be executed.

3. How to select the nth to 2N records. You can use the following SQL statement:

"Select top n * from TABLE_NAME where id not in (select top n id from TABLE_NAME order by id desc )"

4. basic understanding of Top (TOP and set rowcount)

The TOP clause in the SELECT statement limits the number of rows returned by a single query, while set rowcount limits the number of rows affected by subsequent queries. These commands provide high efficiency in many programming tasks.

　　

Set rowcount to SET the maximum number of rows that can be affected in SELECT, INSERT, update or delete statements. These settings take effect immediately when the command is executed and only affect the current session. To remove this restriction, run set rowcount 0. In some actual tasks, TOP or set rowcount is more efficient for programming than standard SQL commands. Let's prove in several examples:

　　

One of the most popular queries in almost all databases is to request the first N items in a list. In the pubs database case, we can find the first five items that sell the best CD. Compare TOP, set rowcount and ansi SQL.

(1) Select title, ytd_salesFrom titlesa Where (select count (*) From titlesb Where B. ytd_sales> a. ytd_sales) <5 Order by ytd_sales DESC

This pure ansi SQL scheme executes an association subquery with low efficiency. In this example, no index is supported on ytd_sales. In addition, this pure standard SQL command does not filter the null values that fall into ytd_sales, and there is no difference between multiple cdns.

(2) set rowcount 5 SELECT title, ytd_salesFROM titlesORDER BY ytd_sales descset rowcount 0

(3) select top 5 title, ytd_salesFROM titlesORDER BY ytd_sales DESC

The second solution uses set rowcount to stop the SELECT query. The third solution uses TOP n to stop the first five rows. In this case, we also need an order by clause to forcibly sort the entire table before obtaining the results. The two query plans are actually the same. However, the key point of TOP over set rowcount is that SET must process the worksheet required BY the order by clause, rather than TOP.

Conclusion: In a large table, we can create an index for the table to avoid sorting. The query will use this index to locate the first five rows and stop.

　　

ROWNUM pseudo column:

1. Use the pseudo column ROWNUM to query the first N records returned by the SELECT statement based on specific conditions. ROWNUM is to add a pseudo column to the result set. It first finds the result set and then adds a column. It is the serial number of the result that meets the condition. It can be sorted from 1 and can only be used <>=! = These comparisons are consistent.

ROWNUM can use ROWNUM = 1 as the condition for a query condition that is equal to a certain value. For example, if ROWNUM = 1 is used to query the first record, no data is found if ROWNUM = 2 is written to the second record, the = of ROWNUM is valid only for 1;

2. Similarly, the condition ROWNUM> n (Natural Number of n> 1) is not true for a query condition greater than a certain value. How can we query records after the first row?

Solution: solves the problem with subqueries, but the alias must be set for rownum, "select * from (select rownum no, id, name from table1) where no> 1 ";

3. ROWNUM can be used for queries smaller than a certain value. select * from table1 where rownum <3

4. ROWNUM and sorting

In Oracle, rownum is the sequence number generated when data is retrieved. To specify rownum row data for the specified sorted data, you need to process it:

"Select rownum, id, name from table1 order by name", the query results are sorted by name, but rownum is not sorted by 1, 2, 3... in the order of record insertion;

Solution: Use subquery, select rownum, id, name from (select * from table1 order by name)