COLLECT WITH DISTINCT AND ORDER BY negates the DISTINCT

View All Scripts Login to Run Script

Script Name COLLECT WITH DISTINCT AND ORDER BY negates the DISTINCT
Description Example showing the use of an ORDER BY clause inside the COLLECT function will negate the DISTINCT clause
Area SQL General / SQL Query
Contributor Sean D. Stuber
Created Wednesday February 27, 2019

Statement 1

SELECT * 
  FROM TABLE( 
           (SELECT CAST(COLLECT(DISTINCT n ORDER BY n) AS ora_mining_number_nt) 
              FROM (SELECT 1 n FROM DUAL 
                    UNION ALL 
                    SELECT 1 n FROM DUAL)) 
       )


COLUMN_VALUE
1
1

2 rows selected.

COLUMN_VALUE
1
1

Statement 2

SELECT * 
  FROM TABLE( 
           (SELECT CAST(COLLECT(DISTINCT n) AS ora_mining_number_nt) 
              FROM (SELECT 1 n FROM DUAL 
                    UNION ALL 
                    SELECT 1 n FROM DUAL)) 
       )


COLUMN_VALUE
1

COLUMN_VALUE
1

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database