Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need to query an SQL database to find all distinct values of one column and I need an arbitrary value from another column. For example, consider the following table with two columns, key and value:

key     value
===     =====
one     test
one     another
one     value
two     goes
two     here
two     also
three   example

I wish to get back one sample row, chosen arbitrarily, from each distinct key, perhaps getting these three rows:

key     value
===     =====
one     test
two     goes
three   example

How can I formulate such a query in SQL?

share|improve this question
    
Which DBMS (Oracle, SQL-Server, DB2, MySQL, Postgres)? –  ypercube Sep 14 '12 at 14:31
    
It is a proprietary system. –  WilliamKF Sep 14 '12 at 14:43
    
Good luck getting help with a proprietary system. Unless the system is widely used or supports a very standardized SQL variant, you have almost no hope of getting an answer without getting it from the people who wrote the system. –  Max Vernon Sep 26 '12 at 5:54
add comment

2 Answers

up vote 6 down vote accepted

The easiest query to write is for MySQL (with not strict ANSI settings). It uses the non-standard construction:

SELECT key, value
FROM tableX
GROUP BY key ;

For other DBMSs, that have window functions (like Postgres, SQL-Server, Oracle, DB2), you can use them like this. The advantage is that you can select other columns in the result as well (besides the key and value) :

SELECT key, value
FROM tableX
    ( SELECT key, value,
             ROW_NUMBER() OVER (PARTITION BY key 
                                ORDER BY whatever)     --- ORDER BY NULL
               AS rn                                   --- for example
      FROM tableX
    ) tmp 
WHERE rn = 1 ;

For older versions of the above and for any other DBMS, a general way that works almost everywhere. One disadvantage is that you cannot select other columns with this approach. Another is that aggregate functions like MIN() and MAX() do not work with some datatypes in some DBMSs (like bit, text, blobs):

SELECT key, MIN(value) AS value
FROM tableX
GROUP BY key ;
share|improve this answer
1  
@WilliamKF If by "chosen arbitrarily" you mean "chosen randomly" then simply replace the ORDER BY whatever in ypercube's query with a call to a function to randomize the results. –  Leigh Riffel Sep 14 '12 at 17:05
    
I think the version with min() (or max()) is the most straighforward solution (and possibly the fastest as well) –  a_horse_with_no_name Sep 14 '12 at 19:53
    
@LeighRiffel It need not be random, any choice, as simple as the first one encountered works fine. –  WilliamKF Sep 14 '12 at 20:20
add comment

For MS-SQl Server:

;with FinalDataset as
(
    select *,
        row_number() over(partition by key order by value) as rownum
    from YourOriginalTable
)
select
   key,
   value
from FinalDataset 
where rownum = 1

Likewise, you could have rownum = 2 for your second result set

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.