AntDB-Oracle Compatibility Developer’s Manual P3–25
Published: 2023-09-06 08:36:17

##### DISTINCT clause

If the DISTINCT clause is specified, then all duplicate records are removed from the result set (only one record is retained from each set of duplicate records). The ALL clause has the opposite effect and will keep all the records, which is the default case.

##### FOR UPDATE clause

The usage method of FOR UPDATE clause is as follows：

FOR UPDATE

The FOR UPDATE clause locks the rows returned by the SELECT command with the same effect as locking the rows for an update operation. This prevents other transactions from modifying or deleting these records until the current transaction ends. That is, other processes attempting to perform an UPDATE, DELETE, or SELECT FOR UPDATE on these rows before the current transaction ends will go into a blocking state. If one or more rows have been locked by an UPDATE, DELETE, or SELECT FOR UPDATE statement in another transaction, then the SELECT FOR UPDATE operation will wait until the other transaction completes, then lock and return the updated row (if the row is deleted, there will be no row)

The FOR UPDATE clause cannot be used when the returned record is not clearly identifiable as a table record. For example, this clause cannot operate with an aggregate function.

**Example**

Join the dept table and the emp table.

```
SELECT d.deptno, d.dname, e.empno, e.ename, e.mgr, e.hiredate
FROM emp e, dept d
WHERE d.deptno = e.deptno;

 deptno |   dname    | empno | ename  | mgr  |      hiredate       
--------+------------+-------+--------+------+---------------------
     30 | SALES      |  7499 | ALLEN  | 7698 | 1981-02-20 00:00:00
     30 | SALES      |  7521 | WARD   | 7698 | 1981-02-22 00:00:00
     20 | RESEARCH   |  7566 | JONES  | 7839 | 1981-04-02 00:00:00
     30 | SALES      |  7654 | MARTIN | 7698 | 1981-09-28 00:00:00
     30 | SALES      |  7698 | BLAKE  | 7839 | 1981-05-01 00:00:00
     10 | ACCOUNTING |  7782 | CLARK  | 7839 | 1981-06-09 00:00:00
     20 | RESEARCH   |  7788 | SCOTT  | 7566 | 1987-04-19 00:00:00
     10 | ACCOUNTING |  7839 | KING   |      | 1981-11-17 00:00:00
     30 | SALES      |  7844 | TURNER | 7698 | 1981-09-08 00:00:00
     20 | RESEARCH   |  7876 | ADAMS  | 7788 | 1987-05-23 00:00:00
     30 | SALES      |  7900 | JAMES  | 7698 | 1981-12-03 00:00:00
     20 | RESEARCH   |  7902 | FORD   | 7566 | 1981-12-03 00:00:00
     10 | ACCOUNTING |  7934 | MILLER | 7782 | 1982-01-23 00:00:00
     20 | RESEARCH   |  7369 | SMITH  | 7902 | 1980-12-17 00:00:00
(14 rows)
```

Calculate the sum of the salaries of all employees and group the results by department number.

```
SELECT deptno, SUM(sal) AS total
FROM emp
GROUP BY deptno;

 deptno |  total   
--------+----------------
     10 |  8750.00
     30 |  9400.00
     20 | 11075.00
(3 rows)
```

The following first calculates the sum of salaries of all employees, groups the results by department number. And show the groups with sums less than 10000.

```
SELECT deptno, SUM(sal) AS total
FROM emp
GROUP BY deptno
HAVING SUM(sal) < 10000;

 deptno |  total  
--------+---------
     10 | 8750.00
     30 | 9400.00
(2 rows)
```

The following 2 examples sort the results by the contents of the second column (dname) in the same way.

```
SELECT * FROM dept ORDER BY dname;

 deptno |   dname    |   loc    
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     40 | OPERATIONS | BOSTON
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
(4 rows)

SELECT * FROM dept ORDER BY 2;

 deptno |   dname    |   loc    
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     40 | OPERATIONS | BOSTON
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
(4 rows)
```