CODEWITHSUNDEEP
sqlsql-servercoldfusioncfml
Organiccat
Organiccat

Reputation: 5651

Select one column DISTINCT SQL

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

Upvotes: 14

Views: 59405

Answers (11)

Corwin Joy
Corwin Joy

Reputation: 643

As noted by John Fiala, the canonical answer in SQL server is to use a group by clause when you want to perform a "distinct" operation over a subset of columns. Why is this the correct canonical answer? Well, you want to pull in columns that are not part of your "distinct" group. Exactly what rows do you want to pull in for these subsidiary columns? Using a group by clause and defining aggregate functions for these subsidiary columns makes your query well-behaved in the sense that you now know how these subsidiary columns are obtained. This article gives more details:

http://weblogs.sqlteam.com/jeffs/archive/2007/10/12/sql-distinct-group-by.aspx

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

Also, it's worth noting that MIN and MAX work on text and several other data types that are not numeric values.

Upvotes: 0

user1133937
user1133937

Reputation: 33

i think

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

worked

Upvotes: 0

Dane
Dane

Reputation: 9827

If you are open to using table variables, you could keep it all within a single database call like this:

DECLARE @attribute_values TABLE (value int, attribute_definition_id int, value_rk uniqueidentifier)

INSERT INTO @attribute_values (value)
SELECT DISTINCT value FROM attribute_values

UPDATE @attribute_values
SET attribute_definition_id = av2.attribute_definition_id,
    value_rk = av2.value_rk
FROM @attribute_values av1
INNER JOIN attribute_values av2 ON av1.value = av2.value

SELECT value, attribute_definition_id, value_rk FROM @attribute_values

Essentially you are creating a limited recordset with the table filled with unique values of 'value', and letting SQL Server fill in the gaps using just one of the matches from the main table.

Edited to add: This syntax works within cfquery just fine.

Upvotes: 2

walming
walming

Reputation: 392

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.

Upvotes: 11

matt.mercieca
matt.mercieca

Reputation: 873

Less elegant than I would like---- it's essentially what you're doing, just in pure SQL--- but it works and can all be done in SQL.

DECLARE @mytable TABLE(mykey NVARCHAR(512), myVal NVARCHAR(512))

DECLARE @keyVal NVARCHAR(512)
DECLARE @depVal NVARCHAR(512)
DECLARE myCursor CURSOR for
   SELECT DISTINCT(value) FROM attribute_values
OPEN myCursor
FETCH NEXT FROM myCursor INTO @keyVal
WHILE @@FETCH_STATUS=0
  BEGIN
     SET @depVal = (SELECT TOP 1 attribute_definition_id FROM attribute_values WHERE VALUE=@keyVal ORDER BY attribute_definition_id)
     INSERT INTO @mytable (mykey, myVal) VALUES (@keyVal, @depVal)
     FETCH NEXT FROM myCursor INTO @keyVal
  END
DEALLOCATE myCursor

SELECT * FROM @mytable

You can add a depVal2 and others using this method.

Upvotes: 0

John Fiala
John Fiala

Reputation: 4581

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.

Upvotes: 2

Patryk Kordylewski
Patryk Kordylewski

Reputation: 1269

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

Upvotes: 8

Adam
Adam

Reputation: 7207

I'm not sure if I entirely understand your set-up, but would something like this work:

SELECT value, attribute_definition_id, value_rk
FROM attribute_values
GROUP BY value
ORDER BY attribute_definition_id;

Again, I'm not real sure which column it is you're trying to limit, or how you're wanting to limit it.

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562320

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.

Upvotes: 9

gfrizzle
gfrizzle

Reputation: 12609

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.

Upvotes: 2

Chris Cudmore
Chris Cudmore

Reputation: 30151

SELECT value, attribute_definition_id, value_rk
FROM attribute_values
WHERE value, value_rk IN (
        SELECT value, max(value_rk)
        FROM attribute_values
        GROUP BY value
)
ORDER BY attribute_definition_id

NOT TESTED!

Upvotes: 1

Related Questions