Added: Working with SQL Server 2000 and 2005, so has to work on both. Also, value_rk is not a number/integer (Error: Operand data type uniqueidentifier is invalid for min operator)
Is there a way to do a single column "DISTINCT" match when I don't care about the other columns returned? Example:
**Table** Value A, Value L, Value P Value A, Value Q, Value Z
I need to return only one of these rows based on what is in the first one (Value A). I still need results from the second and third columns (the second should actually match all across the board anyway, but the third is a unique key, which I need at least one of).
Here's what I've got so far, although it doesn't work obviously:
SELECT value, attribute_definition_id, value_rk FROM attribute_values WHERE value IN ( SELECT value, max(value_rk) FROM attribute_values ) ORDER BY attribute_definition_id
I'm working in ColdFusion so if there's a simple workaround in that I'm open to that as well. I'm trying to limit or "group by" the first column "value". value_rk is my big problem since every value is unique but I only need one.
NOTE: value_rk is not a number, hence this DOES NOT WORK
UPDATE: I've got a working version, it's probably quite a bit slower than a pure SQL version, but honestly anything working at this point is better than nothing. It takes the results from the first query, does a second query except limiting it's results to one, and grabs a matching value_rk for the value that matches. Like so:
<cfquery name="queryBaseValues" datasource="XXX" timeout="999"> SELECT DISTINCT value, attribute_definition_id FROM attribute_values ORDER BY attribute_definition_id </cfquery> <cfoutput query="queryBaseValues"> <cfquery name="queryRKValue" datasource="XXX"> SELECT TOP 1 value_rk FROM attribute_values WHERE value = '#queryBaseValues.value#' </cfquery> <cfset resourceKey = queryRKValue.value_rk> ...
So there you have it, selecting a single column distinctly in ColdFusion. Any pure SQL Server 2000/2005 suggestions are still very welcome 🙂