Re: join group by etc
От | Obe, Regina |
---|---|
Тема | Re: join group by etc |
Дата | |
Msg-id | 53F9CF533E1AA14EA1F8C5C08ABC08D204849C5F@ZDND.DND.boston.cob обсуждение исходный текст |
Ответ на | join group by etc (Peter Jackson <tasmaniac@iprimus.com.au>) |
Ответы |
Re: join group by etc
|
Список | pgsql-novice |
Peter, For grouping - standard ANSI SQL behavior is that all fields in the select need to appear in the group by if they are not aggregated. I think what you are trying to do is probably closer to PostgreSQL's DISTINCT ON feature - try the below SELECT DISTINCT ON(T1.iId) T1.*, T2.ttC, T3.tthD, toD as sort FROM table_one T1 INNER JOIN table_two T2 ON T1.iId = T2.iId INNER JOIN table_three T3 ON T3.fId = T2.fId WHERE T1.tId = '9' and T1.toC = 'o' ORDER BY T1.iId, toD asc; Hope that helps, Regina -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Peter Jackson Sent: Friday, August 08, 2008 8:59 AM To: pgsql Subject: [NOVICE] join group by etc Hi List I'm trying to convert some mysql queries to postgres and hitting a brick wall with the following so was hoping for some hints. table_one - iId,tId,toC,toD,toE table_two - iId,fId,ttC,ttD table_three - fId,tId,tthC,tthD,tthE,tthF table_one data 11,9,o,1218177417,data table_two data 11, 24, test1 11, 25, test2 11, 26, test4 11, 27, test6 table_three data 24,9,area1,t,y,3 25,9,area2,t,y,2 26,9,area3,a,y,1 27,9,area4,y,y,4 mysql query SELECT T1.*, T2.ttC, T3.tthD, toD as sort FROM table_one T1 INNER JOIN table_two T2 ON T1.iId = T2.iId INNER JOIN table_three T3 ON T3.fId = T2.fId WHERE T1.tId = '9' and T1.toC = 'o' GROUP BY T1.iId ORDER BY sort asc; Which in mysql returns 1 row but fails in pg due to the group by. If I add more fields to the group by or remove the group by it returns 4 rows which is incorrect Basically I guess I am asking how I can get the same result in pg without to much change in the sql. Peter J -- Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-novice ----------------------------------------- The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer.
В списке pgsql-novice по дате отправления: