DISTINCT clause used on non-distinct select error

Question

I want to populate a drop down box using the one field from by data, I just want the unique values returned for a set of data from one table. Group by is not implemented in the polyhedra database I am using. Name is primary key for table and all names are unique.

I am trying to run this query:

SELECT DISTINCT userstring05
FROM digital
WHERE userstring05 LIKE '_%'
  AND name LIKE '200-B%'

But I get this error:

DISTINCT clause used on non-distinct select

I believe this was mis-tagged with mysql? – Digital Chris Oct 05 '15 at 16:13 — Digital Chris, Oct 05 '15 at 16:13

score 0 · Answer 1 · answered Oct 05 '15 at 16:05

0

You don't need to use distinct. Use group by to get your result.

SELECT userstring05 FROM digital WHERE userstring05 like '_%'
AND name LIKE '200-B%' GROUP BY userstring05

This will give you the requested result. I haven't tested it since you are not providing some example data but should be ok

answered Oct 05 '15 at 16:05

Lelio Faieta

6,457
7
40
74

The database I am working with has limited functions available: I tired your query and this is result "not implemented: select: GROUP BY" – user5410577 Oct 05 '15 at 16:07
@user5410577 what DB are you using? Both your query and mine should work in mysql – Lelio Faieta Oct 05 '15 at 16:09
Polyhedra Version 8.01.00.04 – user5410577 Oct 05 '15 at 16:39
@user5410577 then why did you tag it with mysql and sql too? – Lelio Faieta Oct 05 '15 at 16:56

xlecoustillier · Accepted Answer · 2015-10-05T16:20:43.613

From ENEA Polyhedra reference:

Inclusion of the distinct clause will generate an error if the select statement could potentially return duplicate rows. Only select statements whose output columns include all the primary key columns of the tables specified in the from clause can be successfully executed with a distinct clause.

So I guess this DBMS doesn't really implement distinct, as this constraint nullifies the interest of using this clause. Unless you join a table without any primary key, maybe ?

EDIT: Seems like this resource is old. Which version of Polyhedra are you using ?

DISTINCT clause used on non-distinct select error

2 Answers2