Select distinct record - help

[cfdeveloper]

Hello everybody,

I was wondering if someone could help me. I am trying to get the distinct record from a select statment. Here is the code.

<cfquery name="getEmployeeDetails" datasource="db">
select distinct reference, uniqueID, firstname, lastname
FROM employee
WHERE reference = 'cfcoder'
ORDER BY reference asc
</cfquery>

This brings back 2 records, and I want it to only return 1 row.

<select name="custname" id="custname">
<cfloop query="getEmployeeDetails">
<option value="#reference#">#reference#</option>
</cfloop>
</select>

The data in the table looks like this

1 cfcoder cf coder
2 CFCODEr cf coder

There are two rows in the table with the same reference. How do I get it to only display 1 record. Can somebody please help

Regards
cfcoder

 

[grtfercho]

use a case function...

select distinct upper(reference) as reference, uniqueID, firstname, lastname
FROM employee
WHERE reference = 'CFCODER'
ORDER BY reference asc



grtfercho çB^]\..
"Imagination is more important than Knowledge" A. Einstein
-----------------------------------------------

http://www.llajta.com

 

[grtfercho]

Need my coffee I guess....

on the condition use

Where upper(reference)='CFCODER'

grtfercho çB^]\..
"Imagination is more important than Knowledge" A. Einstein
-----------------------------------------------

http://www.llajta.com

 

[webmigit]

Your first where line should work... I've never had a problem with case insensitivity on WHERE lines...

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[grtfercho]

I use Oracle and get different results if I don't use the upper() in the where condition. I found that M$SQL doesn't care much about case.

Are you using M$SQL?

grtfercho çB^]\..
"Imagination is more important than Knowledge" A. Einstein
-----------------------------------------------

http://www.llajta.com

 

[webmigit]

I've never used oracle... I've used basic forms of many other database flavors... I mean I didn't usually get into the advanced syntax but I gurantee every db I've used has had a basic string comparison in the where clause.

But I've heard oracle is picky... very very picky...

Though most devvers here use MSSQL, MYSQL, and especially Access.

Good to know though, realistically 'A' should not equal 'a'.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.