how to select distinct records in oracle?

I want to select distinct rows on some condition but when i am adding id column in select statement it returns all the rows???

the below query is working fine

select distinct dst_bnk_acnt_id
,dst_cust_id
,org_cust_id
,dst_pos_id
,pmt_typ_cd
from tb_cm_t_pmt_coll
where org_pos_id = 8 OR dst_pos_id = 8 OR dst_bnk_acnt_id = 1 ;

but when i want to select a latest record using order by id(desc) it returns all rows!

SELECT distinct id
,dst_bnk_acnt_id
,dst_cust_id
,org_cust_id
,dst_pos_id
,pmt_typ_cd
FROM tb_cm_t_pmt_coll
WHERE org_pos_id = 8 OR dst_pos_id = 8 OR dst_bnk_acnt_id = 1
ORDER BY id DESC;

i know 'id' column is primary key and all its values are unique and because of this all rows becomes unique.

i want to select a distinct row using these [dst_bnk_acnt_id,dst_cust_id,org_cust_id,dst_pos_id,pmt_typ_cd] columns only but i also want to order them using id in descending order.

please help.

2 Answers

I don't have your tables so I'll use Scott's sample schema.

Distinct departments and jobs are:

SQL> select distinct deptno, job from emp; DEPTNO JOB
---------- --------- 20 CLERK 30 SALESMAN 20 MANAGER 30 CLERK 10 PRESIDENT 30 MANAGER 10 CLERK 10 MANAGER 20 ANALYST
9 rows selected.

We'd want to sort data per EMPNO (which is something like your ID):

SQL> select distinct deptno, job from emp order by empno;
select distinct deptno, job from emp order by empno *
ERROR at line 1:
ORA-01791: not a SELECTed expression
SQL>

It won't work (as you already know). But, if you use a subquery (or a CTE), then you get this:

SQL> with temp as 2 (select min(empno) id, deptno, job 3 from emp 4 group by deptno, job 5 order by 1 desc 6 ) 7 select deptno, job 8 From temp 9 order by id desc; DEPTNO JOB
---------- --------- 10 CLERK 30 CLERK 10 PRESIDENT 20 ANALYST 10 MANAGER 30 MANAGER 20 MANAGER 30 SALESMAN 20 CLERK
9 rows selected.
SQL>

which means that your query might look like this:

WITH temp AS ( SELECT MIN (id) id, dst_bnk_acnt_id, dst_cust_id, org_cust_id, dst_pos_id, pmt_typ_cd FROM tb_cm_t_pmt_coll WHERE org_pos_id = 8 OR dst_pos_id = 8 OR dst_bnk_acnt_id = 1 GROUP BY dst_bnk_acnt_id, dst_cust_id, org_cust_id, dst_pos_id, pmt_typ_cd) SELECT dst_bnk_acnt_id, dst_cust_id, org_cust_id, dst_pos_id, pmt_typ_cd FROM temp
ORDER BY id DESC;

I would use window functions for that:

select id, dst_bnk_acnt_id, dst_cust_id, org_cust_id, dst_pos_id, pmt_typ_cd
FROM ( SELECT id, dst_bnk_acnt_id, dst_cust_id, org_cust_id, dst_pos_id, pmt_typ_cd, row_number() over (partition by dst_bnk_acnt_id,dst_cust_id,org_cust_id,dst_pos_id,pmt_typ_cd order by id desc) as rn FROM tb_cm_t_pmt_coll WHERE org_pos_id = 8 OR dst_pos_id = 8 OR dst_bnk_acnt_id = 1
)
WHERE rn = 1
ORDER BY id;

The order by id desc in the window function gives the row with the highest id the row number 1, picking the latest id for each combination of the distinct columns.