I am trying to select rows from a database that are both random AND distinct...
Simple no? Well... No. I can select distinct records using SELECT DISTINCT TOP 5 t1.Field1, t2.Field2 FROM t1 JOIN t2 ON (some id's) Now normally to get random records you would just order by newid()... SELECT DISTINCT TOP 5 t1.Field1, t2.Field2 FROM t1 JOIN t2 ON (some id's) ORDER BY newID() But when the DISTINCT keyword is used the "order by" fields must be part of the select... SELECT DISTINCT TOP 5 t1.Field1, t2.Field2, newID() FROM t1 JOIN t2 ON (some id's) ORDER BY newID() Unfortunately, this makes all records unique as they all now have a random field in them! How can I randomise these records while having them remain distinct? -- Jay ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:254585 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
