asked on

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

slightwv (䄆 Netminder)

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.

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Franck Pachot

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.

Ajay Chowdary Kandula

ASKER

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 ?

SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ajay Chowdary Kandula

ASKER

Thanks to all