选择一列DISTINCT SQL [英] Select one column DISTINCT SQL

问题描述

已添加：使用SQL Server 2000和2005，因此必须在两者上工作。此外，value_rk不是数字/整数（错误：操作数数据类型uniqueidentifier对于min运算符无效）

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)

是否有一种方法来执行单列DISTINCT匹配当我不在乎其他列返回吗？示例：

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

我需要只返回其中一行（值A）。我仍然需要第二和第三列的结果（第二个应该实际上匹配所有的板，反正第三是一个唯一的键，我需要至少一个）。

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

我在ColdFusion工作，所以如果有一个简单的解决方法，我也开放。我试图限制或分组第一列值。 value_rk是我的大问题，因为每个值是唯一的，但我只需要一个。

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.

注意：value_rk不是一个数字，因此这不工作

NOTE: value_rk is not a number, hence this DOES NOT WORK

更新：我有一个工作版本，它可能比纯SQL版本慢一些，但是诚实地，任何工作在这一点上比没有什么更好。它从第一个查询获取结果，执行第二个查询，除非将其结果限制为一个，并为匹配的值获取匹配的value_rk。如下：

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>
    ...

所以你有它，在ColdFusion中明确选择一个列。任何纯SQL Server 2000/2005建议仍然是非常欢迎：）

So there you have it, selecting a single column distinctly in ColdFusion. Any pure SQL Server 2000/2005 suggestions are still very welcome :)

推荐答案

这可能工作：

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

未测试。

这篇关于选择一列DISTINCT SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！