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 :)
Adding the DISTINCT keyword to a SELECT query causes it to return only unique values for the specified column list so that duplicate rows are removed from the result set.
Yes, DISTINCT works on all combinations of column values for all columns in the SELECT clause.
If you want the query to return only unique rows, use the keyword DISTINCT after SELECT . DISTINCT can be used to fetch unique rows from one or more columns.
Introduction to SQL DISTINCT operator Note that the DISTINCT only removes the duplicate rows from the result set. It doesn't delete duplicate rows in the table. If you want to select two columns and remove duplicates in one column, you should use the GROUP BY clause instead.
this might work:
SELECT DISTINCT a.value, a.attribute_definition_id,
(SELECT TOP 1 value_rk FROM attribute_values WHERE value = a.value) as value_rk
FROM attribute_values as a
ORDER BY attribute_definition_id
.. not tested.
SELECT a1.value, a1.attribute_definition_id, a1.value_rk
FROM attribute_values AS a1
LEFT OUTER JOIN attribute_values AS a2
ON (a1.value = a2.value AND a1.value_rk < a2.value_rk)
WHERE a2.value IS NULL
ORDER BY a1.attribute_definition_id;
In other words, find the row a1
for which no row a2
exists with the same value
and a greater value_rk
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With