Select statement returns duplicated data

Question

I´m running a simple select stament, and it returns a lot of duplicated records, I need to just return to me one row, I had tried using a group by but it gives me an error, I need to know if there exist any other way to fix this issue.

The query is:

select a.co_id,
       a.customer_id,
       sysadm.func_cap_co_id(co_id, 'RCAT', NULL) RC,
       rowid
from mdsrrtab a 
where a.co_id in ('123456');

The result is :

1. 123456 163972378 20
2. 123456 163972378 20

As you can see it returs to me duplicated information.

When I use the group by it gives me an ORA-00979, I googled it but still I can´t get a solution.

EDIT

I was able to remove the duplicated rows by removing the rowid, thx to @Mr.Llama who made me look at the rowid.

the query is as follows:

  select a.co_id,
     a.customer_id,
     sysadm.func_cap_co_id(co_id, 'RCAT', NULL) RC
     from mdsrrtab a 
     where a.co_id in ('123456')
     group by a.co_id, a.customer_id;

Result

1 158634373 163972378 20

Kind regards.

In this scenario rowid has no value, I use it to manipulate the data with the IDE I'm currently using. — sandatomo, Feb 16 '16 at 14:53
On [ORA-00979](http://stackoverflow.com/questions/1520608/ora-00979-not-a-group-by-expression). Besides, what result do you need? Can you plese post an example of your data and the result you need? — Aleksej, Feb 16 '16 at 14:54

sagi · Accepted Answer · 2016-02-16T15:10:51.213

1

You can either use DISTINCT or GROUP BY

DISTINCT solution:

select distinct a.co_id,
       a.customer_id,
       sysadm.func_cap_co_id(co_id, 'RCAT', NULL) as RC
 from mdsrrtab a 
 where a.co_id in ('123456');

GROUP BY solution:

select a.co_id,
       a.customer_id,
       sysadm.func_cap_co_id(co_id, 'RCAT', NULL) as RC
 from mdsrrtab a 
 where a.co_id in ('123456')
 group by a.co_id,a.customer_id,sysadm.func_cap_co_id(co_id,'RCAT',null);

edited Feb 16 '16 at 15:10

answered Feb 16 '16 at 14:55

sagi

40,026
6
59
84

Hi, I had tried that already but it doesn't work, when I used the Distinct the result is the same, with the group by example it gives me an ORA-00933, I'm still trying, if I can figure this out I´ll post the answer here. – sandatomo Feb 16 '16 at 15:08
Did you copy my solutions and tried them? Or you are assuming you tried it correctly on your own? Try to copy my answers exactly the way they are and tell me what happens . My guess is that the rowid fuc*ed that up.. (as you can see I removed it) so you can select rowid after the distinct/group by – sagi Feb 16 '16 at 15:10
Yes sir, I had copied your solution and its the same result, but I have found the solution or a "workarround" I had removed the rowid, in this case It did the trick, the problem is if I need to update a record I will have to use a for update stament and here is not allowed. – sandatomo Feb 16 '16 at 15:16
Thanks for your advices i really appreciated – sandatomo Feb 16 '16 at 15:16
1

I don't fully understand you.. this 2 queries should work perfectly without the rowid.. what do you mean that you will have to use a for update statement? I'm sure that can be done differently.. and without the rowid my solution did work? @sandatomo – sagi Feb 16 '16 at 15:20
"You should not use ROWID as the primary key of a table. If you delete and reinsert a row with the Import and Export utilities, for example, then its rowid may change. If you delete a row, then Oracle may reassign its rowid to a new row inserted later." https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns008.htm – ESP32 Feb 16 '16 at 15:49
@sagi -- I agree that something is rotten in the state of Denmark. I believe your solution is right. For what it's worth, I *think* the function within the "group by" might not be necessary, since it's only referencing constants and the field already declared in the group by – Hambone Feb 16 '16 at 17:39

Select statement returns duplicated data

1 Answers1