"distinct" in select query

when i use select :

select flattened

(select productid, $support from [model name].[table] )......

i have result with many record same.

so i write :

select flattened

(select distinct productid, $support from [model name].[table] )......

but when i run, it's error. ( don't know what error).

how can i do to get table record with not same record (loop)

note : it write in DTS and i use sql 2000

I assume you mean an Analysis Services query.

Try: SELECT DISTINCT [model name].[table].[productid] FROM [model name]

This should return the distinct list of products

|||

i can't use query like you show.

i want you "distinct " in DTS syntax like that:

select flattened

(select productid, productname, categoryid ,[$probability] from predict([model name].[table name], incluse_statistics) where productid > 1000)

from [model name]

prediction join

shape{....}

appen{....}

on....

How can i put Distinct to have different record?

|||

You can't use DISTINCT in that context. However, I think what you want is to be able to discriminate the records by the user. In this case, the selection will return the product id at most once per input case, but since you don't include an external id, there's no way to discriminate which prediction is for which customer.

I think you need something like

SELECT FLATTENED t.CustomerID, (select ....

This query will return the id from the source data query along with the results of the prediction.