Distinct help

[twcman]

Why would this query return duplicate reportnumber records when I am using the distinct reportnumber in the select statement?

SELECT DISTINCT reportnumber, lpatname + ',' + fpatname AS patname,reportdate,hideview,reportid,physicianid,ssn,signdate,locked,mdedit,transedit FROM reports
WHERE origuserid = 9
ORDER BY reportdate desc


Thanks..............

Chris Scott
The Whole Computer Medical Systems

 

[soloa]

Because you are probably selecting an id field in which that record with that id has a report number. These individual id's may reference a similar report number. Your query will return distinct records, not necessarily reportnumbers.

 

[CFMSACCESS]

because DISTINCT operates on the ENTIRE SELECT LIST
You should use group by instead

GracefulWebDevelopment.com

 

[twcman]

How would you recode the query above or the cfoutput to use group by?



Chris Scott
The Whole Computer Medical Systems