How to select one element from a group?

Question

I have a table t with columns foo and bar:

foo bar
a   x
a   y
b   z

I want to write something like

select foo, count(*) as num, SOMETHING(bar) from t group by foo;

and get

foo num bar
a   2   x    <--- or y, I don't care
b   1   z

I.e., SOMETHING should select an arbitrary value that appeared in a group (first, last, random - I don't care).

Is there a way to do it in sqlite3?

@CL. no, and I am surprised and delighted that it works (I expected an error)! thanks! — sds, Feb 24 '16 at 18:03

score 0 · Accepted Answer · answered Feb 24 '16 at 17:15

0

If you truly don't care, consider using MIN(bar) or MAX(bar). If you want it to be different each time, you'll need to number each value randomly and select the lowest corresponding value. This answer should help you if you choose that route.

answered Feb 24 '16 at 17:15

Mr. Llama

20,202
2
62
115

How to select one element from a group?

1 Answers1