StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSelect one column DISTINCT SQL
text
Body
copied!<p>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)</p> <p>Is there a way to do a single column "DISTINCT" match when I don't care about the other columns returned? Example:</p> <pre><code>**Table** Value A, Value L, Value P Value A, Value Q, Value Z </code></pre> <p>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).</p> <p>Here's what I've got so far, although it doesn't work obviously:</p> <pre><code>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 </code></pre> <p>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.</p> <p>NOTE: value_rk is not a number, hence this DOES NOT WORK</p> <p>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:</p> <pre><code><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> ... </code></pre> <p>So there you have it, selecting a single column distinctly in ColdFusion. Any pure SQL Server 2000/2005 suggestions are still very welcome :)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload