SELECT

Retrieves specified information from a database. A SELECT statement creates a temporary view.

Syntax

query-specification [ [ UNION [ ALL ] query-specification ]... 
[ ORDER BY order-by-expression [ , order-by-expression ]... ] [ FOR 
UPDATE ] 
query-specification ::=	 ( query-specification ) 
| SELECT [ ALL | DISTINCT ] [ top-clause ] select-list 
  FROM table-reference [ , table-reference ]... 
  [ WHERE search-condition ] 
  [ GROUP BY expression [ , expression ]... 
[ HAVING search-condition ] ] 

order-by-expression ::= expression [ CASE (string) | COLLATE collation-
name ] [ ASC | DESC ] 
top-clause ::= TOP number 
select-list ::= * | select-item [ , select-item ]... 
select-item ::= expression [ [ AS ] alias-name ] | table-name . *  
table-reference ::= { OJ outer-join-definition } 
| [db-name.]table-name [ [ AS ] alias-name ] [ WITH (table-hint ) ] 
| [db-name.]view-name [ [ AS ] alias-name ] 
| join-definition 
| ( join-definition ) 
| ( table-subquery )[ AS ] alias-name [ (column-name [ , column-name 
]... ) ] 

outer-join-definition ::= table-reference outer-join-type JOIN table-reference 
ON search-condition 
outer-join-type ::= LEFT [ OUTER ] | RIGHT [ OUTER ] | FULL [ 
OUTER ] 
table-hint ::= INDEX ( index-value [ , index-value ]... ) 
index-value ::= 0 | index-name 
index-name ::= user-defined-name 
join-definition ::= table-reference [ join-type ] JOIN table-reference ON search-
condition 
| table-reference CROSS JOIN table-reference 
| outer-join-definition 

join-type ::= INNER | LEFT [ OUTER ] | RIGHT [ OUTER ] | FULL 
[ OUTER ] 
table-subquery ::= query-specification [ [ UNION [ ALL ] 
query-specification ]... ] 
search-condition ::= search-condition AND search-condition 
| search-condition OR search-condition 
| NOT search-condition 
| ( search-condition ) 
| predicate 

predicate ::= expression [ NOT ] BETWEEN expression AND expression 
| expression-or-subquery comparison-operator expression-or-subquery 
| expression [ NOT ] IN ( query-specification ) 
| expression [ NOT ] IN ( value [ , value ]... ) 
| expression [ NOT ] LIKE value 
| expression IS [ NOT ] NULL 
| expression comparison-operator ANY ( query-specification ) 
| expression comparison-operator ALL ( query-specification ) 
| [ NOT ] EXISTS ( query-specification ) 

comparison-operator ::= < | > | <= | >= | = | <> | != 
expression-or-subquery ::= expression | ( query-specification ) 
value ::= literal | USER | ? 
expression ::= expression - expression 
| expression + expression 
| expression * expression 
| expression / expression 
| expression & expression  
| expression | expression  
| expression ^ expression  
| ( expression ) 
| -expression 
| +expression 
| column-name 
| ? 
| literal 
| set-function 
| scalar-function 
| { fn scalar-function } 
| CASE case_value_expression WHEN when_expression THEN then_expression 
[...] [ ELSE else_expression ] END 
| COALESCE (expression, expression[,...]) 
| IF ( search-condition , expression , expression ) 
| SQLSTATE 
|   subquery-expression 
| NULL 
| : user-defined-name 
| USER 
| @:IDENTITY 
| @:ROWCOUNT 
| @@IDENTITY 
| @@ROWCOUNT 
| @@SPID 

case_value_expression  when_expression, then_expression   else_expression ::= see 
CASE (expression)  
subquery-expression ::= ( query-specification ) 
set-function ::= COUNT (*) 
| COUNT ( [ DISTINCT | ALL ] expression ) 
| SUM ( [ DISTINCT | ALL ] expression ) 
| AVG ( [ DISTINCT | ALL ] expression ) 
| MIN ( [ DISTINCT | ALL ] expression ) 
| MAX ( [ DISTINCT | ALL ] expression ) 

scalar-function ::= see Scalar Functions  

Remarks

This Remarks section contains the following topics:

FOR UPDATE

SELECT FOR UPDATE locks the row or rows within the table that is selected by the query. The record locks are released when the next COMMIT or ROLLBACK statement is issued.

To avoid contention, SELECT FOR UPDATE locks the rows as they are retrieved.

SELECT FOR UPDATE takes precedence within a transactions if statement level SQL_ATTR_CONCURRENCY is set to SQL_CONCUR_LOCK. If SQL_ATTR_CONCURRENCY is set to SQL_CONCUR_READ_ONLY, the database engine does not return an error.

SELECT FOR UPDATE does not support a WAIT or NOWAIT key word. SELECT FOR UPDATE returns status code 84: The record or page is locked if it cannot lock the rows within a brief period (20 re-tries).

Constraints

The SELECT FOR UPDATE statement has the following constraints:

GROUP BY

In addition to supporting a GROUP BY on a column-list, Pervasive PSQL supports a GROUP BY on an expression-list or on any expression in a GROUP BY expression-list. See GROUP BY for more information on GROUP BY extensions. HAVING is not supported without GROUP BY.

Result sets and stored views generated by executing SELECT statements with any of the following characteristics are read-only (they cannot be updated). That is, a positioned UPDATE, a positioned DELETE and an SQLSetPos call to add, alter or delete data is not allowed on the result set or stored view if:

Dynamic parameters (?) are not supported as SELECT items. You may use dynamic parameters in any SELECT statement if the dynamic parameter is part of the predicate. For example, SELECT * FROM faculty WHERE id = ? is valid because the dynamic parameter is part of the predicate.

Note that the SQLExecDirect statement does not support the use of dynamic parameters. For this reason, you cannot use SQL Editor to execute an SQL statement with a dynamic parameter in the predicate. (SQL Editor uses SQLExecDirect.)

You may use variables as SELECT items only within stored procedures. See CREATE PROCEDURE .

Use of Aliases

Aliases may appear in a WHERE, HAVING, ORDER BY, or GROUP BY clause. Alias names must differ from any column names within the table. The following statement shows the use of aliases, "a" and "b," in a WHERE clause and in a GROUP BY clause.

SELECT Student_ID a, Transaction_Number b, SUM (Amount_
Owed) FROM Billing WHERE a < 120492810 GROUP BY a, b 
UNION SELECT Student_ID a, Transaction_Number b, SUM 
(Amount_Paid) FROM Billing WHERE a > 888888888 GROUP BY 
a, b 

SUM and DECIMAL Precision

When using the SUM aggregate function to sum a field that is of type DECIMAL, the following rules apply:

The precision of the result is 74, while the scale is dependent on the column definition.

The result may cause an overflow error if a number with precision greater than 74 is calculated (a very large number indeed). If an overflow occurs, no value is returned, and SQLSTATE is set to 22003, indicating a numeric value is out of range.

Subqueries

A subquery is a SELECT statement with one or more SELECT statements within it. A subquery produces values for further processing within the statement. The maximum number of nested subqueries allowed within the topmost SELECT statement is 16.

The following types of subqueries are supported:

ORDER BY clauses are not allowed in a subquery clause.

Correlated subquery predicates in the HAVING clause which contain references to grouped columns are not supported.

Expression subqueries allow the subquery within the SELECT list. For example, SELECT (SELECT SUM(c1) FROM t1 WHERE t1.c2 = t1.(c2) FROM t2. Only one item is allowed in the subquery SELECT list. For example, the following statement returns an error because the subquery SELECT list contains more than one item: SELECT p.id, (SELECT SUM(b.amount_owed), SUM(b.amount_paid) FROM billing b) FROM person p.

A subquery as an expression may be correlated or non-correlated. A correlated subquery references one or more columns in any of the tables in the topmost statement. A non-correlated subquery references no columns in any of the tables in the topmost statement. A subquery connected with the operators IN, EXISTS, ALL, or ANY is not considered an expression.

Both correlated and non-correlated subqueries can return only a single value. For this reason, both correlated and non-correlated subqueries are also referred to as scalar subqueries.

Scalar subqueries may appear in the DISTINCT, GROUP BY, and ORDER BY clause.

You may use a subquery on the left-hand side of an expression:

Expr-or-SubQuery CompareOp Expr-or-SubQuery 

where Expr is an expression, and CompareOp is one of:

Optimization

Left-hand subquery behavior has been optimized for IN, NOT IN, and =ANY in cases where the subquery is not correlated and any join condition is an outer join. Other conditions may not be optimized. Here is an example of a query that meets these conditions:

SELECT count(*) FROM person WHERE id IN 
        (SELECT faculty_id FROM class) 

Performance improves if you use an index in the subquery because Pervasive PSQL optimizes a subquery based on the index. For example, the subquery in the following statement is optimized on student_id because it is an index in the Billing table:

SELECT (SELECT SUM(b.amount_owed) FROM billing b WHERE 
b.student_id = p.id) FROM person p 

UNION in Subquery

Parentheses on different UNION groups within a subquery are not allowed. Parentheses are allowed within each SELECT statement.

For example, the parenthesis following "IN" and the last parenthesis are not allowed the following statement:

SELECT c1 FROM t5 WHERE c1 IN ( (SELECT c1 FROM t1 UNION 
SELECT c1 FROM t2) UNION ALL (SELECT c1 FROM t3 UNION 
SELECT c1 from t4) ) 

Table Subqueries

Table subqueries can be used to combine multiple queries into one detailed query. A table subquery is a dynamic view, which is not persisted in the database. When the topmost SELECT query completes, all resources associated with table subqueries are released.

Table Hint

The table hint functionality allows you to specify which index, or indexes, to use for query optimization. A table hint overrides the default query optimizer used by the database engine.

If the table hint specifies INDEX(0), the engine performs a table scan of the associated table. (A table scan reads each row in the table rather than using an index to locate a specific data element.)

If the table hint specifies INDEX(index-name), the engine uses index-name to optimize the table based on restrictions of any JOIN conditions, or based on the use of DISTINCT, GROUP BY, or ORDER BY. If the table cannot be optimized on the specified index, the engine attempts to optimize the table based on any existing index.

If you specify multiple index names, the engine chooses the index that provides optimal performance or uses the multiple indexes for OR optimization. An example helps clarify this. Suppose that you have the following:

CREATE INDEX ndx1 on t1(c1) 
CREATE INDEX ndx2 on t1(c2) 
CREATE INDEX ndx3 on t1(c3) 
SELECT * FROM t1 WITH (INDEX (ndx1, ndx2)) WHERE c1 = 1 
AND c2 > 1 AND c3 = 1 

The database engine uses ndx1 to optimize on c1 = 1 rather than using ndx2 for optimization. Ndx3 is not considered because the table hint does not include ndx3.

Now consider the following:

SELECT * FROM t1 WITH (INDEX (ndx1, ndx2)) WHERE (c1 = 1 
OR c2 > 1) AND c3 = 1 

The engine uses both ndx1 and ndx2 to optimize on (c1 = 1 OR c2 > 1).

The order in which the multiple index names appear in the table hint does not matter. The database engine chooses from the specified indexes the one(s) that provides for the best optimization.

Duplicate index names within the table hint are ignored.

---

Note
The table hint functionality is intended for advanced users. Typically, table hints are not required because the database query optimizer usually picks the best optimization method.

---

Restrictions

Examples

This simple SELECT statement retrieves all the data from the Faculty table.

SELECT * FROM Faculty 

This statement retrieves the data from the person and the faculty table where the id column in the person table is the same as the id column in the faculty table.

SELECT Person.id, Faculty.salary FROM Person, Faculty 
WHERE Person.id = Faculty.id  

The following example retrieves student_id and sum of the amount_paid where it is greater than or equal to 100 from the billing table. It then groups the records by student_id.

SELECT Student_ID, SUM(Amount_Paid) 
FROM Billing 
GROUP BY Student_ID  
HAVING SUM(Amount_Paid) >=100.00 

If the expression is a positive integer literal, then that literal is interpreted as the number of the column in the result set and ordering is done on that column. No ordering is allowed on set functions or an expression that contains a set function.

FOR UPDATE

The following example uses table t1 to demonstrate the use of FOR UPDATE. Assume that t1 is part of the DEMODATA sample database. The stored procedure creates a cursor for the SELECT FOR UPDATE statement. A loop fetches each record from t1 and, for those rows where c1=2, sets the value of c1 to four.

The procedure is called by passing the value "2" as the IN parameter.

The example assumes that two users, A and B, are logged in to DEMODATA. User A performs the following:

DROP TABLE t1 
CREATE TABLE t1 (c1 INTEGER, c2 INTEGER) 
INSERT INTO t1 VALUES (1,1) 
INSERT INTO t1 VALUES (2,1) 
INSERT INTO t1 VALUES (1,1) 
INSERT INTO t1 VALUES (2,1) 
CREATE PROCEDURE p1 (IN :a INTEGER) 
AS  
BEGIN  
DECLARE :b INTEGER; 
DECLARE :i INTEGER;  
DECLARE c1Bulk CURSOR FOR SELECT * FROM t1 WHERE 
c1 = :a FOR UPDATE;  
START TRANSACTION; 
OPEN c1Bulk;  
BulkLinesLoop:  
  LOOP  
    FETCH NEXT FROM c1Bulk INTO :i;  
    IF SQLSTATE = '02000' THEN  
      LEAVE BulkLinesLoop;  
    END IF;  
    UPDATE SET c1 = 4 WHERE CURRENT OF c1Bulk;  
  END LOOP;  
CLOSE c1Bulk;  
SET :b = 0; 
WHILE (:b < 100000) do 
  BEGIN 
    SET :b = :b + 1; 
  END; 
END WHILE; 
COMMIT WORK; 
END;  

CALL p1(2) 

Notice that a WHILE loop delays the COMMIT of the transaction. During that delay, assume that User B attempts to update t1 with call SQLExecDirect "UPDATE t1 SET c1=3 WHERE c1=2." A status code 84 is returned to User B because those rows are locked by the SELECT FOR UPDATE statement from User A.

The following example uses table t1 to demonstrate how SELECT FOR UPDATE locks records when the statement is used outside of a stored procedure. Assume that t1 is part of the DEMODATA sample database.

The example assumes that two users, A and B, are logged in to DEMODATA. User A performs the following:

DROP TABLE t1 
CREATE TABLE t1 (c1 INTEGER, c2 INTEGER) 
INSERT INTO t1 VALUES (1,1) 
INSERT INTO t1 VALUES (2,1) 
INSERT INTO t1 VALUES (1,1) 
INSERT INTO t1 VALUES (2,1) 

Call SQLSetConnectAttr with option SQL_AUTOCOMMIT and attribute SQL_AUTOCOMMIT_OFF.

Call SQLExecDirect "SELECT * FROM t1 WHERE c1 = 2 FOR UPDATE."

The two records where c1 = 2 are locked until User A issues a COMMIT WORK or ROLLBACK WORK statement.

User B attempts to update t1 with call SQLExecDirect "UPDATE t1 SET c1=3 WHERE c1=2." A status code 84 is returned to User B because those rows are locked by the SELECT FOR UPDATE statement from User A.

Now assume that User A issues call SQLEndTran with option SQL_COMMIT. The two records where c1 = 2 are unlocked.

User B could now issue call SQLExecDirect "UPDATE t1 SET c1=3 WHERE c1=2" and change the values for c1.

approximate-numeric-literal

SELECT * FROM results WHERE quotient =-4.5E-2 
INSERT INTO results (quotient) VALUES (+5E7) 

between-predicate

The syntax expression1 BETWEEN expression2 and expression3 returns TRUE if expression1 >= expression2 and expression1<= expression3. FALSE is returned if expression1 >= expression3, or is expression1 <= expression2.

Expression2 and expression3 may be dynamic parameters (for example, SELECT * FROM emp WHERE emp_id BETWEEN ? AND ?)

The next example retrieves the first names from the person table whose ID fall between 10000 and 20000.

SELECT First_name FROM Person WHERE ID BETWEEN 10000 AND 
20000 

correlation-name

Both table and column correlation names are supported.

The following example selects data from both the person table and the faculty table using the aliases T1 and T2 to differentiate between the two tables.

SELECT * FROM Person t1, Faculty t2 WHERE t1.id = t2.id  

The correlation name for a table name can also be specified in using the FROM clause, as seen in the following example.

SELECT a.Name, b.Capacity FROM Class a, Room b  
WHERE a.Room_Number = b.Number 

exact-numeric-literal

SELECT car_num, price FROM cars WHERE car_num =49042 AND 
price=49999.99 

in-predicate

This selects the records from table Person table where the first names are Bill and Roosevelt.

SELECT * FROM Person WHERE First_name IN ('Roosevelt', 
'Bill') 

set-function

The following example selects the minimum salary from the Faculty table.

SELECT MIN(salary) FROM Faculty 

MIN(expression), MAX(expression), SUM(expression), AVG(expression), COUNT(*), and COUNT(expression) are supported.

COUNT(expression) counts all non-NULL values for an expression across a predicate. COUNT(*) counts all values, including NULL values.

The following example counts all the rows in q where a+b does not equal NULL.

SELECT COUNT(a+b) FROM q 

date-literal

Date constants may be expressed in SQL statements as a character string or embedded in a vendor string. SQL_CHAR and the vendor string representation are treated as a value of type SQL_DATE. This becomes important when conversions are attempted.

Pervasive PSQL partially supports extended SQL grammar, as outlined in this function.

The next two statements return all the classes whose start date is after 1995-06-05.

SELECT * FROM Class WHERE Start_Date > '1995-06-05' 
SELECT * FROM Class WHERE Start_Date > {d '1995-06-05'} 

Pervasive PSQL supports the following date literal format: 'YYYY-MM-DD'.

Dates may be in the range of year 0 to 9999.

time-literal

The following two statements retrieve records from the class table where the start time for the classes is 14:00:00.

SELECT * FROM Class WHERE Start_time = '14:00:00' 
SELECT * FROM Class WHERE Start_time = {t '14:00:00'} 

Pervasive PSQL supports the following time literal format: 'HH:MM:SS'.

Time constants may be expressed in SQL statements as a character string or embedded in a vendor string. Character string representation is treated as a string of type SQL_CHAR and the vendor string representation as a value of type SQL_TIME.

Pervasive PSQL partially supports extended SQL grammar, as outlined in this function.

timestamp-literal

Timestamp constants may be expressed in SQL statements as a character string or embedded in a vendor string. Pervasive PSQL treats the character string representation as a string of type SQL_CHAR and the vendor string representation as a value of type SQL_TIMESTAMP. Pervasive PSQL partially supports extended SQL grammar, as outlined in this function.

The next two statements retrieve records from the Billing table where the start day and time for the log is 1996-03-28 at 17:40:49.

SELECT * FROM Billing WHERE log = '1996-03-28 
17:40:49.0000000' 
SELECT * FROM Billing WHERE log = {ts '1996-03-28 
17:40:49.0000000'} 

Pervasive PSQL supports the following time literal format: 'YYYY-MM-DD HH:MM:SS.SSSSSSS'. Note that Pervasive PSQL ignores the fractional seconds when processing Timestamp values, but the MKDE does not.

string-literal

Literal strings are represented as single-quoted strings. If the string itself contains a single-quote or apostrophe, the character must be preceded by another single-quote.

SELECT * FROM t1 WHERE c1 = 'Roberta's Restaurant' 
SELECT STREET FROM address WHERE city LIKE 'San%' 

date arithmetic

SELECT * FROM person P, Class C WHERE p.Date_Of_Birth < 
' 1973-09-05' and c.Start_date >{d '1995-05-08'} + 30 

Pervasive PSQL supports adding or subtracting an integer from a date where the integer is the number of days to add or subtract, and the date is embedded in a vendor string. (This is equivalent to executing a convert on the date).

You may also subtract one date from another to yield a number of days.

IF

The IF system scalar function provides conditional execution based on the truth value of a condition

This expression prints the column header as "Prime1" and amount owed as 2000 where the value of the column amount_owed is 2000 or it prints a 0 if the value of the amount_owed column is not equal to 2000.

SELECT Student_ID, Amount_Owed, 
IF (Amount_Owed = 2000, Amount_Owed, Convert(0, SQL_
DECIMAL)) "Prime1" 
FROM Billing 

From table Class, the following example prints the value in the Section column if the section is equal to 001, else it prints "xxx" under column header Prime1

Under column header Prime2, it prints the value in the Section column if the value of the section column is equal to 002, or else it prints "yyy."

SELECT ID, Name, Section, 
IF (Section = '001', Section, 'xxx') "Prime1", 
IF (Section = '002', Section, 'yyy') "Prime2" 

FROM Class

You can combine header Prime1 and header Prime2 by using nested IF functions. Under column header Prime, the following query prints the value of the Section column if the value of the Section column is equal to 001 or 002. Otherwise, it print "xxx."

SELECT ID, Name, Section, 
IF (Section = '001', Section, IF(Section = '002', 
Section, 'xxx')) Prime 

FROM Class

Multi-database Join

When needed, a database name may be prepended to an aliased table name in the FROM clause, to distinguish among tables from two or more different databases that are used in a join.

All of the specified databases must be serviced by the same database engine. They do not need to reside on the same physical volume. The current database may be secure or non-secure, but all other databases in the join must be non-secure. With regard to Referential Integrity, all RI keys must exist within the same database.

Literal database names are not permitted in the select-list or in the WHERE clause. If you wish to refer to specific columns in the select-list or in the WHERE clause, you must use an alias for each specified table. See examples.

Assume two separate databases, "accounting" and "customers," exist on the same server. You can join tables from the two databases using table aliases and SQL syntax similar to the following example:

SELECT ord.account, inf.account, ord.balance, 
inf.address  
FROM accounting.orders ord, customers.info inf  
WHERE ord.account = inf.account 

In this example, the two separate databases are "acctdb" and "loandb." The table aliases are "a" and "b," respectively.

SELECT a.loan_number_a, b.account_no, a.current_bal, 
b.balance  
FROM acctdb.ds500_acct_master b LEFT OUTER JOIN 
loandb.ml502_loan_master a ON (a.loan_number_a = 
b.loan_number)  
WHERE a.current_bal <> (b.balance * -1)  
ORDER BY a.loan_number_a 

left outer join

The following example shows how to access the "Person" and "Student" tables from the DEMODATA database to obtain the Last Name, First Initial of the First Name and GPA of students. With the LEFT OUTER JOIN, all rows in the "Person" table are fetched (the table to the left of LEFT OUTER JOIN). Since not all people have GPA's, some of the columns have NULL values for the results. This is how outer join works, returning non-matching rows from either table.

SELECT Last_Name,Left(First_Name,1) AS First_
Initial,Cumulative_GPA AS GPA FROM "Person" 
LEFT OUTER JOIN "Student" ON Person.ID=Student.ID 
ORDER BY Cumulative_GPA DESC, Last_Name 

Assume that you want to know everyone with perfectly rounded GPA's and have them all ordered by the length of their last name. Using the MOD statement and the LENGTH scalar function, you can achieve this by adding the following to the query:

WHERE MOD(Cumulative_GPA,1)=0 ORDER BY LENGTH(Last_
Name) 

right outer join

The difference between LEFT and RIGHT OUTER JOIN is that all non matching rows show up for the table defined to the right of RIGHT OUTER JOIN. Change the query for LEFT OUTER JOIN to include a RIGHT OUTER JOIN instead. The difference is that the all non-matching rows from the right table, in this case "Student," show up even if no GPA is present. However, since all rows in the "Student" table have GPA's, all rows are fetched.

SELECT Last_Name,Left(First_Name,1) AS First_
Initial,Cumulative_GPA AS GPA FROM "Person" 
RIGHT OUTER JOIN "Student" ON Person.ID=Student.ID 
ORDER BY Cumulative_GPA DESC, Last_Name 

Cartesian join

A Cartesian join is the matrix of all possible combinations of the rows from each of the tables. The number of rows in the Cartesian product equals the number of rows in the first table times the number of rows in the second table.

Assume you have the following tables in your database:

The following performs a Cartesian JOIN on these tables:

SELECT * FROM Addr,Loc 

This results in the following:

DISTINCT

You can use DISTINCT with SUM, AVG, COUNT, MIN, and MAX (but it does not change results with MIN and MAX). DISTINCT eliminates duplicate values before calculating the sum, average or count.

Suppose you want to know the salaries for different departments including the minimum, maximum and salary, and you want to remove duplicate salaries. The following statement would do this, excluding the computer science department:

SELECT dept_name, MIN(salary), MAX(salary), AVG(DISTINCT 
salary) FROM faculty WHERE dept_name<>'computer science' 
GROUP BY dept_name 

If you wanted to include duplicate salaries, you would use:

SELECT dept_name, MIN(salary), MAX(salary), AVG(salary) 
FROM faculty WHERE dept_name<>'computer science' GROUP 
BY dept_name 

TOP

You may limit the number of rows returned by a single SELECT statement by using the keyword TOP in the statement and specifying a value for the maximum number of rows to return.

The specified number of rows must be a literal positive number. It is defined as a 32-bit unsigned integer.

A SELECT statement can include both TOP and an ORDER BY clause. If so, the database engine generates a temporary table and populates it with the query's entire result set if no index can be used for the ORDER BY. The rows in the temporary table are ordered as specified by the ORDER BY clause and the TOP number of rows in the ordered result set are returned.

Views that contain a TOP clause may be joined with other tables or views.

The main difference between TOP and SET ROWCOUNT is that TOP affects only the current statement, while SET ROWCOUNT affects all subsequent statements issued during the current database session.

If both SET ROWCOUNT and TOP are applied to a given query, the query returns a number of rows equal to the lesser of the two values.

Cursor Types and TOP

A SELECT query with a TOP clause used in a cursor implicitly changes the cursor type under several circumstances. When using the table below, remember that any SELECT with ORDER BY on an unindexed column requires a temporary table. If the ORDER BY is on an indexed column, then a temporary table is not required.

SELECT TOP 10 * FROM person  
		-- returns 10 rows 
SET ROWCOUNT = 5; 
SELECT TOP 10 * FROM person; 
		-- returns 5 rows 
SET ROWCOUNT = 12; 
SELECT TOP 10 * FROM person ORDER BY id; 
		-- returns the first 10 rows of the full list ordered by column id. 

The following examples show a variety of behaviors when TOP is used in views, unions, or subqueries.

CREATE VIEW v1 (c1) AS SELECT TOP 10 id FROM person; 
CREATE VIEW v2 (d1) AS SELECT TOP 5 c1 FROM v1; 
SELECT * FROM v2 -- returns 5 rows 
SELECT TOP 10 * FROM v2 -- returns 5 rows 
SELECT TOP 0 * FROM v2 -- returns 0 rows 
SELECT TOP 2 * FROM v2 -- returns 2 rows 
SELECT TOP 10 id FROM person UNION SELECT TOP 13 faculty_
id FROM class  
 		-- returns 14 rows 
SELECT TOP 10 id FROM person UNION ALL SELECT TOP 13 
faculty_id FROM class  
 		-- returns 23 rows 
SELECT id FROM person WHERE id IN (SELECT TOP 10 faculty_
id from class)  
		-- returns 4 rows 
SELECT id FROM person WHERE id >= any (SELECT TOP 10 
faculty_id from class)  
		-- returns 1493 rows 

Incorrect Examples

The following SELECT statements generate error messages, because the view defined contains a TOP clause, and thus cannot be involved in a join.

CREATE VIEW v1 (c1) AS SELECT TOP 10 id FROM person  
SELECT * FROM v1 INNER JOIN person ON v1.c1 = person.id  
		-- returns an error message 
SELECT * FROM person V v1 ON person.c1 = v1.id  
		-- returns an error message 
SELECT * FROM person, v1  
		-- returns an error message 

Table Subquery

The following example provides the last name of the student and the amount the student owes for all students with an ID greater than 714662900.

SELECT p_last_name, b_owed FROM  
   (SELECT id, last_name FROM person) p (p_id, p_last_
name), 
   (SELECT student_id, SUM (amount_owed) FROM billing 
   GROUP BY student_id) b (b_id, b_owed) 
WHERE p.p_id = b.b_id AND p.p_id > 714662900 
ORDER BY p_last_name ASC 

Table Hints

The examples for table hints use table t1, t2, or both. You can create and populate the example tables with the following SQL.

DROP TABLE t1 
CREATE TABLE t1 (c1 INTEGER, c2 INTEGER) 
INSERT INTO t1 VALUES (1,10) 
INSERT INTO t1 VALUES (1,10) 
INSERT INTO t1 VALUES (2,20) 
INSERT INTO t1 VALUES (2,20) 
INSERT INTO t1 VALUES (3,30) 
INSERT INTO t1 VALUES (3,30) 
CREATE INDEX it1c1 ON t1 (c1) 
CREATE INDEX it1c1c2 ON t1 (c1, c2) 
CREATE INDEX it1c2 ON t1 (c2) 
CREATE INDEX it1c2c1 ON t1 (c2, c1) 
DROP TABLE t2  
CREATE TABLE t2 (c1 INTEGER, c2 INTEGER) 
INSERT INTO t2 VALUES (1,10) 
INSERT INTO t2 VALUES (1,10) 
INSERT INTO t2 VALUES (2,20) 
INSERT INTO t2 VALUES (2,20) 
INSERT INTO t2 VALUES (3,30) 
INSERT INTO t2 VALUES (3,30) 

Note that certain restrictions apply to the use of table hints. See Restrictions for SQL examples.

The following example optimizes on index it1c1c2.

SELECT * FROM t1 WITH (INDEX(it1c1c2)) WHERE c1 = 1 

Contrast this with the following example, which optimizes on index it1c1 instead of on it1c2 because the restriction consists only of "c1 = 1." If a query specifies an index that cannot be used to optimize the query, the hint is ignored.

SELECT * FROM t1 WITH (INDEX(it1c2)) WHERE c1 = 1 

The following example performs a table scan of table t1.

SELECT * FROM t1 WITH (INDEX(0)) WHERE c1 = 1 

The following example optimizes on indexes it1c1c2 and it1c2c1.

SELECT * FROM t1 WITH (INDEX(it1c1c2, it1c2c1)) WHERE c1 
= 1 OR c2 = 10 

The following example using a table hint in the creation of a view. When all records are selected from the view, the SELECT statement optimizes on index it1c1c2.

DROP VIEW v2 
CREATE VIEW v2 as SELECT * FROM t1 WITH (INDEX(it1c1c2)) 
WHERE c1 = 1 
SELECT * FROM v2 

The following example uses a table hint in a subquery and optimizes on index it1c1c2.

SELECT * FROM (SELECT c1, c2 FROM t1 WITH 
(INDEX(it1c1c2)) WHERE c1 = 1) AS a WHERE a.c2 = 10 

The following example optimizes the query based on the c1 = 1 restriction and optimizes the GROUP BY clause based on index it1c1c2.

SELECT c1, c2, count(*) FROM t1 WHERE c1 = 1 GROUP BY c1, 
c2 

The following example optimizes on index it1c1 and, unlike the previous example, optimizes only on the restriction and not on the GROUP BY clause.

SELECT c1, c2, count(*) FROM t1 WITH (INDEX(it1c1)) WHERE 
c1 = 1 GROUP BY c1, c2 

Since the GROUP BY clause cannot be optimized using the specified index, it1c1, the database engine uses a temporary table to process the GROUP BY.

The following example uses a table hint in a JOIN clause and optimizes on index it1c1c2.

SELECT * FROM t2 INNER JOIN t1 WITH (INDEX(it1c1c2)) ON 
t1.c1 = t2.c1  

Contrast this with the following statement, which does not use a table hint and optimizes on index it1c1.

SELECT * FROM t2 INNER JOIN t1 ON t1.c1 = t2.c1  

The following example uses a table hint in a JOIN clause to perform a table scan of table t1.

SELECT * FROM t2 INNER JOIN t1 WITH (INDEX(0)) ON t1.c1 
= t2.c1  

Contrast this with the following example which also performs a table scan of table t1. However, because no JOIN clause is used, the statement uses a temporary table join.

SELECT * FROM t2, t1 WITH (INDEX(0)) WHERE t1.c1 = t2.c1 

See Also

Global Variables

JOIN

SET ROWCOUNT