DISTINCT Rows not getting returned with newID() function

[murphyhg]

How can I get DISTINCT rows returned with this setup?
When I run my query like this I get DISTINCT rows.

SELECT DISTINCT t.tbt_no, t.country, t.date_issued, t.products, t.final_comment_date
FROM OPENDATASOURCE('SQL', 'Data Source=TSSQL;User Id=myid;Password=mypassword').mydb.dbo.thisdb t
WHERE EXISTS (SELECT COUNTRY_NAME FROM dbo.REGIONS WHERE REG_ID = 5 AND t.country = COUNTRY_NAME) AND (t.final_comment_date >= { fn NOW() })
ORDER BY t.tbt_no

I want to be able to display the results randomly so I tried adding the newID function and this inserts in all of the results as if the DISTINCT keyword was not there.

SELECT DISTINCT t.tbt_no, t.country, t.date_issued, t.products, t.final_comment_date, newID AS NUM
FROM OPENDATASOURCE('SQL', 'Data Source=TSSQL;User Id=myid;Password=mypassword').mydb.dbo.thisdb t
WHERE EXISTS (SELECT COUNTRY_NAME FROM dbo.REGIONS WHERE REG_ID = 5 AND t.country = COUNTRY_NAME) AND (t.final_comment_date >= { fn NOW() })
ORDER BY NUM

Thanks for your help.

 

[RiverGuy]

Here's what you want to do:

SELECT x.*
FROM

(SELECT DISTINCT Column1, Column2, Column3
FROM SomeTable) x

ORDER BY NEWID()

 

[murphyhg]

I'm not quite sure what you are asking me to do? What table does X represent?

 

[RiverGuy]

its a derived table. Put your query minus the order by and newid stuff inside the parentheses.

 

[murphyhg]

I tried this but the syntax does not appear to be correct.
What is wrong. I am getting this error back. Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'X'. It is talking about the last declaration of X.

SELECT X.* FROM FROM OPENDATASOURCE('SQL', 'Data Source=TSSQL;User Id=myid;Password=mypassword').mydb.dbo.thisdb x

(SELECT DISTINCT t.tbt_no, t.country, t.date_issued, t.products, t.final_comment_date, newID() AS NUM
FROM OPENDATASOURCE('SQL', 'Data Source=TSSQL;User Id=myid;Password=mypassword').mydb.dbo.thisdb t
WHERE EXISTS (SELECT COUNTRY_NAME FROM dbo.REGIONS WHERE REG_ID = 5 AND t.country = COUNTRY_NAME) AND (t.final_comment_date >= { fn NOW() })) X
ORDER BY NUM

 

[RiverGuy]

More like this:

SELECT x.*
FROM

(SELECT DISTINCT t.tbt_no,  t.country, t.date_issued, t.products, t.final_comment_date
FROM OPENDATASOURCE('SQL', 'Data Source=TSSQL;User Id=myid;Password=mypassword').mydb.dbo.thisdb t
WHERE EXISTS (SELECT COUNTRY_NAME FROM dbo.REGIONS WHERE REG_ID = 5 AND t.country = COUNTRY_NAME) AND (t.final_comment_date >= { fn NOW() })) x

ORDER BY NEWID()