SQL - Select distinct on two column

Question

I have this table 'words' with more information:

+---------+------------+-----------
| ID      |ID_CATEGORY | ID_THEME |
+---------+------------+-----------
| 1       |      1     |     1
| 2       |      1     |     1
| 3       |      1     |     1
| 4       |      1     |     2
| 5       |      1     |     2
| 6       |      1     |     2
| 7       |      2     |     3
| 8       |      2     |     3
| 9       |      2     |     3
| 10      |      2     |     4
| 11      |      2     |     4
| 12      |      3     |     5
| 13      |      3     |     5
| 14      |      3     |     6
| 15      |      3     |     6
| 16      |      3     |     6

And this query that gives to me 3 random ids from different categories, but not from different themes too:

SELECT Id
FROM words
GROUP BY Id_Category, Id_Theme
ORDER BY RAND()
LIMIT 3

What I want as result is:

+---------+------------+-----------
| ID      |ID_CATEGORY | ID_THEME |
+---------+------------+-----------
| 2       |      1     |     1
| 7       |      2     |     3
| 14      |      3     |     6

That is, repeat no category or theme.

Try to use GROUP BY like this: http://stackoverflow.com/a/54430/2717958 — Michal Krasny, Mar 16 '15 at 09:05
The general GROUP BY rule says: "If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function." Your id column doesn't follow this. — jarlh, Mar 16 '15 at 09:06
Try this `select distinct id,id_cat,id_theme from ( select id,id_cat,id_theme,id_cat+id_theme tc from words)y limit 3 ` — Vivek S., Mar 16 '15 at 10:14

JotaBe · Accepted Answer · 2015-03-16T09:53:12.357

1

When you use GROUP BY you cannot include in the select list a column which is not being ordered. So, in your query it's impossible to inlcude Id in the select list.

So you need to do something a bit more complex:

SELECT Id_Category, Id_Theme,
    (SELECT Id FROM Words W 
      WHERE W.Id_Category = G.Id_Category AND W.Id_Theme = G.Id_Theme
      ORDER BY RAND() LIMIT 1
    ) Id
FROM Words G
GROUP BY Id_Category, Id_Theme
ORDER BY RAND()
LIMIT 3

NOTE: the query groups by the required columns, and the subselect is used to take a random Id from all the possible Ids in the group. Then main query is filtered to take three random rows.

edited Mar 16 '15 at 09:53

answered Mar 16 '15 at 09:09

JotaBe

38,030
8
98
117

This doesn't work for me. No error appears just a blank web page, maybe a syntax problem? – ismaestro Mar 23 '15 at 10:00
do you have a db manager, like pgAdmin, to run the query and see the results without the web app in the middle? – JotaBe Mar 23 '15 at 10:03
everything works fine with the sql query. There was another problem with PHP... ;) – ismaestro Mar 23 '15 at 10:25

SQL - Select distinct on two column

1 Answers1