40

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?

2
  • 2
    Which DBMS (Oracle, SQL-Server, DB2, MySQL, Postgres)? Sep 14 '12 at 14:31
  • 1
    It is a proprietary system.
    – WilliamKF
    Sep 14 '12 at 14:43
48

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 ;

In recent version (5.7 and 8.0+) where the strict settings and ONLY_FULL_GROUP_BY are the default, you can use the ANY_VALUE() function, added in 5.7:

SELECT key, ANY_VALUE(value) AS 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 ;

PostgreSQL has a special non-standard DISTINCT ON operator that can also be used. The optional ORDER BY is for selecting which row from every group should be selected:

SELECT DISTINCT ON (key) key, value
FROM tableX
-- ORDER BY key, <some_other_expressions> ;
2
  • 2
    @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. Sep 14 '12 at 17:05
  • 1
    @LeighRiffel It need not be random, any choice, as simple as the first one encountered works fine.
    – WilliamKF
    Sep 14 '12 at 20:20
4

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

2

Similar to accepted answer, but instead of min() or max() you can use array_agg()

SELECT key, (array_agg(value))[1] AS value
FROM tableX
GROUP BY key ;

You can optionally order values inside array to select biggest or smallest of them:

SELECT key, (array_agg(value) ORDER BY value DESC)[1] AS value
FROM tableX
GROUP BY key ;

(checked on PostgreSQL)

1

you can use the query below :

select key, value
  from (select key,
               value,
               row_number() over(partition by key order by value) rnum
          from YourOriginalTable )
 where rnum = 1;
3
  • I'm afraid that doesn't meet the "one sample row, chosen arbitrarily" requirement. And it is not "PLSQL".
    – mustaccio
    Mar 8 at 17:42
  • @mustaccio Thanks for your practical and informative comment. I will delete my answer but before that , could you please explain more how it does not meet the "one sample row" , chosen "arbitrary" ?
    – Pantea
    Mar 8 at 19:51
  • @mustaccio . Another thing is that "JP Chauhan" answer is similar to mine. Isn't it? so in what way exactly my way is different from his? Thanks in advance
    – Pantea
    Mar 8 at 19:52
1

If you use PostgreSQL, there is also a aggregate function called first() (or last()) from an extension called first_last_agg, which does exactly this: returning the first value (in any defined order):

SELECT key, first(value) AS value
FROM tab
GROUP BY key ;
-1
SELECT key, MIN(value) AS value
FROM tableX
GROUP BY key ;
1
  • This just repeats one of the existing answers, without any explanation.
    – mustaccio
    Dec 21 '20 at 14:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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