Solution for all Cases
Martin, April 28, 2014 - 1:17 pm UTC
Hi Kumar,
To select this for all Cases in your tabel I would:
CREATE TABLE T
(
CASE_NO VARCHAR2(18 CHAR),
VERSION_NO NUMBER(3),
ID NUMBER(8)
);
Insert into T (CASE_NO, VERSION_NO, ID) Values ('Case_A', 0, 10037844);
Insert into T (CASE_NO, VERSION_NO, ID) Values ('Case_A', 1, 10037844);
Insert into T (CASE_NO, VERSION_NO, ID) Values ('Case_A', 2, 10037844);
Insert into T (CASE_NO, VERSION_NO, ID) Values ('Case_A', 2, 10016256);
Insert into T (CASE_NO, VERSION_NO, ID) Values ('Case_A', 3, 10037844);
Insert into T (CASE_NO, VERSION_NO, ID) Values ('Case_A', 3, 10016256);
Insert into T (CASE_NO, VERSION_NO, ID) Values ('Case_A', 3, 10060800);
Insert into T (CASE_NO, VERSION_NO, ID) Values ('Case_B', 0, 99997844);
Insert into T (CASE_NO, VERSION_NO, ID) Values ('Case_B', 1, 99997844);
Insert into T (CASE_NO, VERSION_NO, ID) Values ('Case_B', 2, 99997844);
Insert into T (CASE_NO, VERSION_NO, ID) Values ('Case_B', 2, 99996256);
Insert into T (CASE_NO, VERSION_NO, ID) Values ('Case_B', 3, 99997844);
Insert into T (CASE_NO, VERSION_NO, ID) Values ('Case_B', 3, 99996256);
Insert into T (CASE_NO, VERSION_NO, ID) Values ('Case_B', 3, 99990800);
COMMIT;
select case_no,version_no,id
from
(
select case_no,version_no,id,count(id) over (Partition by id) cnt from t
)
where cnt = 1;
CASE_NO VERSION_NO ID
------------------ ---------- ----------
Case_A 3 10060800
Case_B 3 99990800
2 rows selected.
Not correct
Narendra Gupta, May 08, 2014 - 9:03 am UTC
This may not be correct.
update t set id='10060800' where id='99990800';
1 row updated.
select case_no,version_no,id
from (
select case_no,version_no,id,count(id) over (Partition by id) cnt from t
)
where cnt = 1
/
no rows selected
Play around..
sadek, May 13, 2014 - 3:27 pm UTC
You may try 'ditinct', least, first, last... in a subquery.
Orace is full of Built-in functions.
A reader, November 03, 2014 - 9:27 pm UTC
select emp_no,count(*) from emp
group by emp_no having count(*) > 1
will list you those employees with duplicate employee numbers. Then you need to nail down to which record you want to keep by actually looking at the data in other columns that makes sense to you. Hope it helps.
A reader, November 12, 2014 - 2:12 pm UTC
select emp_no,count(*) from employee
group by emp_no
having count(*) = 1 will give you non duplicte ids
A reader, November 19, 2014 - 5:57 pm UTC
Hi
Did you try my solution? Did it work?
November 26, 2014 - 9:09 pm UTC
it is not my goal to test your code, why would i?
and no, it won't work. you are querying EMP and EMPNO.
if you want to participate, you can test your code and give a full response. Something like "will list you those employees with duplicate employee numbers. Then you need to nail down to which
record you want to keep by actually looking at the data in other columns that makes sense to you. " isn't really very useful.
it doesn't answer the query.
it doesn't come close to even using the set of data the problem is centered around.
it doesn't come close to giving them any clue as to how to "actually looking at the data" might be accomplished.