• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

Difference in the number of rows returned

select distinct col1 , col2 from table;

//75048 Records Returned

with temp as (select distinct col1, col2 from table)
select count(*) from temp
;

// 75833 Records Returned

I am not able to figure out why the difference despite the same query.. something elusive hidden somewhere.. requesting all the experts to help me please make me understand.. is this something basic or is this something normal confusion for everyone... Please help me understand
0
XxtremePro
Asked:
XxtremePro
  • 2
  • 2
  • 2
  • +1
3 Solutions
 
slightwv (䄆 Netminder) Commented:
This was posted in a 10.x Zone.  Please confirm you are running 10g.

I have seen something like this in 11g with results caching.
0
 
sdstuberCommented:
Just in case,  I'll ask the obvious....

Were 785 rows added to the table between the time you ran the queries?

assuming no...

10g has a few bugs related to WITH clauses,  you may have encountered one. Are you fully patched?

A little more obscure,  check for  equivalencies created with dbms_advanced_rewrite

select * from DBA_REWRITE_EQUIVALENCES
0
 
Franck PachotCommented:
Hi,
Can you try:
select count(*) from (select distinct col1 , col2 from table)

Open in new window

in order to see if it comes with a subquery factoring bug ?
Can you show the execution plan with predicates (set autotrace trace explain)
Regards,
Franck.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
XxtremeProAuthor Commented:
Thanks Franck

I am a newbie in the world of programming with database, your statements are way advanced to what I know in sql.

ok now looking at what I would need I would say the need is to pick up just two values from the table as per requirement

select distinct col1, col2 from table1;

col1, col2 both are primary keys. Would the above query ensure that I get only distinct col1 values and col2 values?? If not then what should be done to ensure both the col1 and associate col2 values are distinct ?
0
 
sdstuberCommented:
yes that will make them distinct

like this...

1,1
1,2
1,3
a,b
c,d
e,f
e,g
e,h


but not necessarily ordered
0
 
slightwv (䄆 Netminder) Commented:
>>col1, col2 both are primary keys

Are they the only two columns defined in the Primary Key?

>>. Would the above query ensure that I get only distinct col1 values and col2 values??

Yes.  Just set up a simple test on your development database to see what effects different queries have.



Now that we have answered that, please answer the other questions we have asked.
drop table tab1 purge;
create table tab1(col1 char(1), col2 char(1));

insert into tab1 values('a','b');
insert into tab1 values('a','c');
insert into tab1 values('a','c');
commit;

select distinct col1, col2 from tab1;

Open in new window

0
 
XxtremeProAuthor Commented:
Thanks to all
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now