Distinct command doesn't work properly...

[tommyboy]

hi guyz,

I wonder why my sql command not realy trimming properly as im using distinct command instead...

my sql command is ==> strSQl = "select distinct missing,empNo,reason from [tempMissing] order by empNo"

the sample result obtained was attached here:-

 

[BadgerByte]

Hey,

When you use DISTINCT with multiple columns it will return all distinct combinations of those columns.

Hope this helps.

 

[tommyboy]

yeah BadgerByte,
i juz discover it tis morning..

erm..is tat any other way of commanding so tat i can select all those three items but the result cannot hv repeated empNo..?

realy appreciated if u cld help

 

[tommyboy]

well i hv tried this...

strSQL="SELECT DISTINCT MAX(missing),empNo,MAX(reason) FROM [tempMissing] ORDER BY empNo"

But it promted me an error sayin ..

"Column 'tempMissing.empNo' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause."

do u hv any idea wats ter for..?

 

[WellsCarrie]

Do this instead.
SELECT distinct empNo, MAX(missing), MAX(reason)
FROM [tempMissing]
GROUP BY empNo
ORDER BY empNo