Thread: Error with DISTINCT
testdb=#SELECT DISTINCT table_2.f1, table_1.f2, '2000-08-22' AS testdb-#date FROM table_1, table_2 WHERE table_1.f1 = table_2.f1; ERROR: Unable to identify an ordering operator '<' for type 'unknown' Use an explicit ordering operator or modifythe query I must execute this query : testdb=#SELECT table_2.f1, table_1.f2, '2000-08-22' AS date FROM testdb-#table_1, table_2 WHERE table_1.f1 = table_2.f1 GROUP BY testdb-#table_2.f1, table_1.f2; Is it a bug ? Thanks in advance. Jerome.
Jerome Raupach <jraupach@intelcom.fr> writes: > testdb=#SELECT DISTINCT table_2.f1, table_1.f2, '2000-08-22' AS > testdb-#date FROM table_1, table_2 WHERE table_1.f1 = table_2.f1; > ERROR: Unable to identify an ordering operator '<' for type 'unknown' > Use an explicit ordering operator or modify the query > Is it a bug ? No, I don't think so. The system has no way to intuit what datatype you consider '2000-08-22' to be. SELECT DISTINCT table_2.f1, table_1.f2, '2000-08-22'::date ... would work. regards, tom lane
Hi, try this: testdb=#SELECT DISTINCT table_2.f1, table_1.f2, date('2000-08-22') AS testdb-#date FROM table_1, table_2 WHERE table_1.f1 = table_2.f1; Jerome Raupach wrote: > testdb=#SELECT DISTINCT table_2.f1, table_1.f2, '2000-08-22' AS > testdb-#date FROM table_1, table_2 WHERE table_1.f1 = table_2.f1; > ERROR: Unable to identify an ordering operator '<' for type 'unknown' > Use an explicit ordering operator or modify the query > > I must execute this query : > > testdb=#SELECT table_2.f1, table_1.f2, '2000-08-22' AS date FROM > testdb-#table_1, table_2 WHERE table_1.f1 = table_2.f1 GROUP BY > testdb-#table_2.f1, table_1.f2; > > Is it a bug ? > Thanks in advance. > > Jerome. -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 jliang@ipinc.com www.ipinc.com