asked on

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)

Mike Eghtebas

It is not very clear what you want the solution to do.

I want to [???] the common object ID ... for table

slightwv (䄆 Netminder)

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.

CalmSoul

ASKER

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 ....

Mike Eghtebas

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)

ASKER CERTIFIED 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

slightwv (䄆 Netminder)

Suppose I should post the alternative in case it is faster:

select some_val, max(another_val)
from
...
group by some_val