Select random row for each group

Question

I have a table like this

ID    ATTRIBUTE
 1    A
 1    A
 1    B
 1    C
 2    B
 2    C
 2    C
 3    A
 3    B
 3    C

I'd like to select just one random attribute for each ID. The result therefore could look like this (although this is just one of many options

ATTRIBUTE
B
C
C

This is my attempt on this problem

SELECT
  "ATTRIBUTE"
FROM
  (
  SELECT
    "ID",
    "ATTRIBUTE",
    row_number() OVER (PARTITION BY "ID" ORDER BY random()) rownum
  FROM
    table
  ) shuffled
WHERE
  rownum = 1

however, I don't know if this is a good solution, as I need to introduce row numbers, which is a bit cumbersome.

Do you have a better one?

Solution

select distinct on (id) id, attribute
from like_this
order by id, random()

If you only need the attribute column:

select distinct on (id) attribute
from like_this
order by id, random()

Notice that you still need to order by id first as it is a column of the distinct on.

If you only want the distinct attributes:

select distinct attribute
from (
    select distinct on (id) attribute
    from like_this
    order by id, random()
) s

Comments

[speendo]

but this would return EVERY attribute in a random order, not just one per ID, or do I misunderstand your code?

[Clodoaldo Neto]

@speendo The distinct on (id) makes it return one only row per id

[Dan Oak]

laughed like this :D

[Roelant]

No syntax like this in mysql is there? :)

[Adithya Sama]

wouldn't the result always have the same order, even though its random the first time.