The upstairs example is classic, but two points are added: 1. the FIRST_VALUE function is different between the use of DISTINCT and the use of DISTINCT. The FIRST_VALUE function of DISTINCT is similar to the first () function in SQLServer to obtain the first data in all groups. If the DISTINCT and FIRST_VALUE functions are not used
The upstairs example is classic, but two points are added: 1. the FIRST_VALUE function is different between the use of DISTINCT and the use of DISTINCT. The use of the FIRST_VALUE function of DISTINCT is similar to the first () in SQL Server to obtain the first data in all groups. If the DISTINCT and FIRST_VALUE functions are not used
The upstairs example is classic, but there are two additional points:
1. The FIRST_VALUE function is different when DISTINCT is used and DISTINCT is not used. The FIRST_VALUE function of DISTINCT is similar to the first () function in SQL Server to obtain the first data in all groups.
If the DISTINCT and FIRST_VALUE functions are not used, the first value (first_value) of the group is returned for each NAME, which is grouped by POS based on the example above)
SELECT DISTINCT
FIRST_VALUE (NAME) OVER
(Partition by pos order by age desc)
AS MAXAGE_NAME
, FIRST_VALUE (NAME) OVER
(Partition by pos order by age asc)
AS MINAGE_NAME
, AVG (AGE) OVER
(Partition by pos)
AS AVG_AGE
, POS
FROM EMP
ORDER BY POS
Running result
MAXAGE_NAME MINAGE_NAME AVG_AGE POS
1 E C33.6666666666667 PL
2 E C33.6666666666667 PL
3 E C33.6666666666667 PL
4 a B 22.5 PM
5 a B 22.5 PM
2. Performance,
The use of DISTINCT usually results in a loss of SQL running performance, but the specific loss is not intuitive,
Based on the analysis results of Toad (Tools of Oracle Application Developers), the same SQL statement uses DISTINCT and ROWNUM = 1. The performance loss of DISTINCT is as high as 30%, the evidence is given below.
How to Write DISTINCT
SELECT DISTINCT
FIRST_VALUE (NAME) OVER
(Partition by id order by age desc)
AS MAXAGE_NAME
FROM EMP
ORDER BY POS
ROWNUM = 1
SELECT MAXAGE_NAME
(SELECT
FIRST_VALUE (NAME) OVER
(Partition by id order by age desc)
AS MAXAGE_NAME
FROM EMP
Order by pos) T
Where rownum = 1