Need a better way (query request)

Database
Enthusiast

Need a better way (query request)

All,
i have a table that has the following information

product_id product_desc eff_ts
100 product_description_2 2007-07-10 08:49:59
100 product_description_2 2007-07-10 08:40:23
100 product_description_1 2007-07-09 13:22:04
200 Newproduct 2007-07-10 08:49:07
200 Newproduct 2007-07-10 08:40:52

now i need to query this table to get the following result.

product_id product_desc
100 product_description_1
100 product_description_2

Basically i am trying to display the product_id that has multiple descriptions.
if the description is repeated more than once i just need to see it once.

this is the query i am using and it works

select a.product_id, a.product_desc
from devdata.feroz_source a, (select distinct product_id
from devdata.feroz_source
qualify rank() over (partition by product_id order by product_desc) > 1) b(product_id)
where a.product_id = b.product_id
group by 1,2

is there a better way of writing this query?

Thanks
4 REPLIES
Enthusiast

Re: Need a better way (query request)

Efficiency of the query is determined by its demographics and statistics.

I have attempted to write the query in a different way to get the same result.Ofcourse, this might not be an efficient way to address the issue as this one executes the sub-query for each row in the outer table.

But,let me know if this helps

CREATE MULTISET TABLE SAMPLES.test4
(
id INTEGER,
name VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( id );

BTEQ -- Enter your DBC/SQL request or BTEQ command:
select * from test4;

*** Query completed. 7 rows found. 2 columns returned.
*** Total elapsed time was 1 second.

id name
----------- ---------------
2 cccc
3 eeee
2 dddd
1 aaaa
1 bbbb
1 pppp
1 aaaa

BTEQ -- Enter your DBC/SQL request or BTEQ command:
select distinct id,name from test4 T where 1 < (select count(id) from test4 T2 where T2.id=T.id);

*** Query completed. 5 rows found. 2 columns returned.
*** Total elapsed time was 1 second.

id name
----------- ---------------
1 aaaa
1 bbbb
1 pppp
2 cccc
2 dddd

Enthusiast

Re: Need a better way (query request)

Leo,

That looks cool.

Junior Contributor

Re: Need a better way (query request)

This one not only looks cool, it returns the expected answer set ;-)

select product_id, product_desc
from feroz_source
group by 1,2
qualify count(*) over (partition by product_id) > 1

Dieter
Enthusiast

Re: Need a better way (query request)

Dieter That was really cool (and ofcourse it returned the expected answer set)
Thanks.
Leo, I appreciate your input too.

I did not want to use the distinct because of efficiency.