unique count

Hello Experts:

I have three queries which are giving me list of object ID ...

I want to the common object ID ... for table

Query 1 Result:
1
2
3
4

Query 2 Result:
5
6
7
8

Query 3 Result:
7
3
2
1
9

I want to get unique count:

output count = 9 (in query 3 results 7,3,2,1 exist in query result 1, 2 thats why they were ignored)
LVL 5
CalmSoulAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
It is not very clear what you want the solution to do.

I want to [???] the common object ID ... for table
slightwv (䄆 Netminder) Commented:
Two ways I can think of:
select count(distinct object ID) from(
query1
union all
query2
union all
query3
)


or

select count(object ID) from(
query1
union
query2
union
query3
)

Not sure which one is more efficient since I don't have your tables and data.

If the three queries access the same tables then we might be able to make it easier but would need the queries.
CalmSoulAuthor Commented:
slightwv:

Thanks - UNION will work ...

But here is the problem I just got into... I have two lookup values

object ID, and version

Union will give me unique object ID across query result ... How can I pick max (version)?

For example I have 1 value in all three query results

1,2
1,3
1,5

I want to pick unique 1 but max version

1,5 should be the result ....
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Mike EghtebasDatabase and Application DeveloperCommented:
why not

output count = 5, 6, and 8 (in query 2 results 7 exist in other queries)

or

output count = 4 (in query 1 results 1, 2, 3 exist in other queries)
slightwv (䄆 Netminder) Commented:
>>But here is the problem I just got into... I have two lookup values

Please post sample data and expected results.

Based on what you posted it will likely use ROW_NUMBER to resolve the issue:

select some_val, another_val from (
   select some_val, another_val, row_number() (partition by some_val order by some_val, another_val desc) rn
from ...
)
where rn=1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
Suppose I should post the alternative in case it is faster:

select some_val, max(another_val)
from
...
group by some_val
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.