Thunderstone Support Forums

Hi,

I have the following table in Texis:
Documents:
documentUrl: char containing the url of the document
CategoryID: char id of the category the document belongs to
PlainText: varchar containing a plain text version of the document.

Now I want to run a query that returns the most significant document for each category (the number of category evolves constantly and can be very large, so I cannot really)
I tried using the Distinct reserved word, but with not much success.
I tried the following statement.
Select distint CategoryID, documentUrl where PlainText like 'my keyword' order by $rank.
But it returns the same categoryID several times.
What am I doing wrong?

Thanks,
/Tony.

(P.S: I don't use Vortex. I need to use a sql query directly)

Distinct applies to the entire set of columns selected so you get the same ID because the Url is different. To get distinct ID's select just ID. I can't think of any way in SQL to do quite what you want. I think it'll require some processing in whatever language you are using. You probably want to use likep with no order by instead of like order by rank to the result set is inherently ordered. You might want to turn on likepallmatch though depending on your needs.