How to make XML Application Query Resource results "distinct"?

Rhythmyx Technical Discussion
1

Using Rhythmyx 6.5.2

We have three content types: GenericPage, ProductDescriptionPage, ArticlePage. All three have a common set of shared fields and they are all published to the same database table Pages. Fields published include ContentID, DisplayTitle, and Path. ContentID is the primary key in the Pages table.

In another different content type, I want to have a DropDownSingle for which the choices are all of the published ProductDescriptionPages content items. I know I can create an XML Application with a Query Resource based on sys_Lookup.dtd to populate the list of choices.

Where I’m having an issue is in the results returned by that application. I tried doing a join between the Pages table on the published database and the CT_GS_PRODUCTDESCRIPTIONPAGE table on the CMS Repository. And that actually works great, up until I have a ProductDescriptionPage with more than one revision; then it ends up with duplicate entries in the result (one for each revision).

Is there a way to make the results in my Lookup Query “distinct”? Or is there another/better way to do this Lookup Query so I don’t have this issue?

Note: I realize I could add a Type column to my publishing database and do it that way, but as that column would only be used for this one purpose, I’d rather that be a last resort option.

Thanks.

2

We solved our issue using revision id. You can use the below query for reference. For this to work you have to select the Manaul Query in the selector

SELECT *
from ct_category cc
where community_id = ‘:“PSXSingleHtmlParameter/sys_community”’
and cc.revisionid =(select max (revisionid)
from ct_category icc
where icc.contentid =cc.contentid)";