Using Order By with Distinct on a Join (PLSQL)

Question

I have written a join on some tables and I have ordered the data using two levels of ordering - one of which is the primary key of one table.

Now, with this data sorted I want to then exclude any duplicates from my data using an in-line view and the DISTINCT clause - and this is where I am coming unstuck.

I seem to be able to either sort the data OR distinct it, but never both at the same time. Is there a way around this or have I stumbled upon the SQL equivalent of the uncertainty principle?

This code returns the data sorted, but with duplicates

                SELECT 
                    ada.source_tab source_tab
                  , ada.source_col source_col
                  , ada.source_value source_value
                  , ada.ada_id ada_id
                FROM 
                    are_aud_data ada
                  , are_aud_exec_checks aec
                  , are_audit_elements ael
                WHERE 
                      aec.aec_id = ada.aec_id
                  AND ael.ano_id = aec.ano_id
                  AND aec.acn_id = 123456
                  AND ael.ael_type = 1
                ORDER BY 
                  CASE
                    WHEN source_tab = 'Tab type 1' THEN 1
                    WHEN source_tab = 'Tab type 2' THEN 2
                    ELSE 3
                  END
              ,ada.ada_id ASC;

This code removes the duplicates, but I lose the order...

SELECT DISTINCT source_tab, source_col, source_value FROM (
                SELECT 
                    ada.source_tab
                  , ada.source_col source_col
                  , ada.source_value source_value
                  , ada.ada_id ada_id
                FROM 
                    are_aud_data ada
                  , are_aud_exec_checks aec
                  , are_audit_elements ael
                WHERE 
                      aec.aec_id = ada.aec_id
                  AND ael.ano_id = aec.ano_id
                  AND aec.acn_id = 123456
                  AND ael.ael_type = 1
                ORDER BY 
                  CASE
                    WHEN source_tab = 'Tab type 1' THEN 1
                    WHEN source_tab = 'Tab type 2' THEN 2
                    ELSE 3
                  END
              ,ada.ada_id ASC
     )
;

If I try and include 'ORDER BY ada_id' at the end of the outer select, I get the error message 'ORA-01791: not a SELECTed expression' which is infuriating me!!

score 0 · Answer 1 · answered Feb 20 '12 at 16:35

0

Why don't you include ada_id at the selected fields of the outer query?

answered Feb 20 '12 at 16:35

linepogl

9,147
4
34
45

ada_id is a primary key, so every record is unique (This would nullify my DISTINCT clause). I would have thought that I would be able to include "ORDER BY ada_id" in the outer query, but as I say - it returns an error :S – TommyWylde Feb 20 '12 at 16:41

score 0 · Answer 2 · answered Feb 20 '12 at 16:39

;WITH CTE AS
(
                  SELECT 
                    ada.source_tab source_tab
                  , ada.source_col source_col
                  , ada.source_value source_value
                  , ada.ada_id ada_id
                  , ROW_NUMBER() OVER (PARTITION BY [COLUMNS_YOU_WANT TO BE DISTINCT] 
                    ORDER BY [your_columns])  rn
                FROM 
                    are_aud_data ada
                  , are_aud_exec_checks aec
                  , are_audit_elements ael
                WHERE 
                      aec.aec_id = ada.aec_id
                  AND ael.ano_id = aec.ano_id
                  AND aec.acn_id = 356441
                  AND ael.ael_type = 1
                ORDER BY 
                  CASE
                    WHEN source_tab = 'Licensed Inventory' THEN 1
                    WHEN source_tab = 'CMDB'               THEN 2
                    ELSE 3
                  END
              ,ada.ada_id ASC
)
select * from CTE WHERE rn<2

score 0 · Answer 3 · answered Feb 20 '12 at 16:48

it seems that the ada_id is meaningless in the outer query. you have removed all those values to boil it down to the distinct source_tab and source_col...

what would you expect the order to be?

you want maybe the minimum ada_id for each table and column set to be the driver for the order - (although the table name seems appropriate to me)

include the minimum ada_id in the inner query (you'll need a group by clause) then reference that in the outer query and sort on it.

Using Order By with Distinct on a Join (PLSQL)

3 Answers3