Select one column DISTINCT SQL

Question

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 :)

Solution 1

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.

Solution 2

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.

Solution 3

This should work for PostgreSQL, i don't know which dbms you use.

SELECT DISTINCT ON (value)
  value, 
  attribute_definition_id, 
  value_rk
FROM 
  attribute_values
ORDER BY
  value, 
  attribute_definition_id

PostgreSQL Docs

Solution 4

Is this what you're looking for?

SELECT value, attribute_definition_id, value_rk
FROM attribute_values av1
WHERE value_rk IN (
        SELECT max(value_rk)
        FROM attribute_values av2
        WHERE av2.value = av1.value
)
ORDER BY attribute_definition_id

If value_rk is unique, this should work.

Solution 5

Okay, here's my assumptions:

Standard SQL Server

value_rk is not a numeric value, but value and attribute_definition_id are numeric.

SELECT value_rk, MIN(value) as value, MIN(attribute_definition_id) as attribute_definition_id
FROM attribute_values
GROUP BY value_rk
ORDER BY MIN(attribute_definition_id)

If one of those fields isn't numeric, then it'll require more thought - please let us know.

View more solutions

Comments

[Organiccat]

My fault, I posted without testing myself, I updated that value_rk is not a number (max/min won't work on it)

[topski]

max/min will work on varchar values in SQL2005. What db are you using?

[Organiccat]

Actually testing on 2005, but the production server is 2000. I get a data type mismatch error when trying to run min/max on it (added to top of OP post).

[Admin]

OP post is like PIN number or SSN number

[Admin]

It fails to work in your scenario because IN expects a single column list of values. WHERE foo in (SELECT bar, max(baz) ... is wrong no matter if baz is a number or not.

[Organiccat]

I tried moving the max out the top select with the same result. For some reason, having the max in the WHERE portion didn't actually throw an error, but didn't affect the results either way.

[Stefan Steiger]

The only problem here is MS-SQL doesn't know the DISTINCT ON, only DISTINCT in all selected columns...

[Sam]

This is a great solution because it will work with most (all?) RDBMS's. I've been spoiled by PostgreSQL's DISTINCT ON which is simple to type, but nonstandard and not implemented in SQL Server, among others.