PostgreSQL - error in query

Question

I have this query:

SELECT DISTINCT par.id, par.title, cod.id AS id_codebook, cod.title AS title_codebook
FROM catalog_parameter_codebook cod
JOIN catalog_parameter par ON (par.id=cod.id_parameter)
JOIN catalog_parameter_product_value_parameter_codebook_mm codmm ON (cod.id=codmm.id_parameter_codebook)
JOIN catalog_parameter_product_value pv ON (pv.id=codmm.id_parameter_product_value)
JOIN catalog_product p ON (p.id=pv.id_product)
WHERE p.state=2 AND p.hidden=0 AND par.type=2 AND par.display_in_parameter_search=1 AND par.hidden=0 
ORDER BY par.sorting, cod.title

And i get this error:

LINE 8: ORDER BY par.sorting, cod.title

ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

I am a total beginner with postgreSQL. Thank you.

LongBeard_Boldy · Accepted Answer · 2015-11-16 09:06:23Z

2

as error message states, par.sorting must be in the select list

if you use distinct or group by, all columns you are sorting by, must be in the select column list

SELECT DISTINCT par.id, par.title, cod.id AS id_codebook, cod.title AS title_codebook, par.sorting
FROM catalog_parameter_codebook cod
JOIN catalog_parameter par ON (par.id=cod.id_parameter)
JOIN catalog_parameter_product_value_parameter_codebook_mm codmm ON  (cod.id=codmm.id_parameter_codebook)
JOIN catalog_parameter_product_value pv ON (pv.id=codmm.id_parameter_product_value)
JOIN catalog_product p ON (p.id=pv.id_product)
WHERE p.state=2 AND p.hidden=0 AND par.type=2 AND par.display_in_parameter_search=1 AND par.hidden=0 
ORDER BY par.sorting, cod.title

answered Nov 16, 2015 at 9:06

LongBeard_Boldy

9128 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kevas Over a year ago

Ok, I understand it, but when I add par.sorting to the SELECT list, i don´t get a output which required. I get duplicated rows.

LongBeard_Boldy Over a year ago

why do you get duplicate? give us a primitive table schema, preferably with a small sampledata, and explain what do you want to acomplish

Collectives™ on Stack Overflow

PostgreSQL - error in query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related