I have written a query in sql
, which contains group by
clause.
The group by clause is used on an attribute which is neither primary key or unique key, I have read at various places that when we are using mysql it generally takes any arbitrary row, but in postgreSql, it doesn't behave in the same manner. So I have used aggregation function while writing the query.
The query I have written is :
select user_id, min(priority) from table where is_active = 'false' group by user_id;
This query is working fine, but when I try to get information about any other attribute I am getting an error.
New Query:
select id,user_id, min(priority) from table where is_processed='false' group by user_id;
and the error is :
column "table.id" must appear in the GROUP BY clause or be used in an aggregate function.
Am I missing something ??
Explanation:
As per the answers suggests, I should use id or anything else that I need to select after group by clause. But It will change the change the concept. Say I have a table "Pending_Entries". It consists some attributes like id,userd_id,is_active and priority. Now I need to find the
user_id and the minimum priority
corresponding to that user_id
. For that I used the initial query and it is working perfectly fine. Now suppose I need the id
too.
What should I do ?
The error is normal; MySQL is not correct. In Postgres, you can use
distinct on
:select distinct on (user_id) t.*
from table t
where is_processed = 'false'
order by user_id, prioirty asc;
In MySQL, your query works syntactically, but it is not doing what you want. It is returning an arbitrary value for the columns not in the
group by
and not in aggregation functions.
There are many ways to have a query work in both systems:
select t.*
from table t
where t.is_processed = 'false' and
t.priority = (select min(t2.priority)
form table t2
where t2.user_id = t.user_id and t2.is_processed = 'false'
);
0 comments:
Post a Comment