Обсуждение: 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
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.
Peter Jackson <tasmaniac@iprimus.com.au> writes:
> 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.
Egad. *Which* row does it return, and why? Anyone reading the SQL
spec would have to say that this query's behavior is undefined:
there are multiple t2 and t3 rows joining to each T1 row, hence
no principled way to decide which ttC and tthD values to output
for a given T1.iId value.
(One of mysql's more unlovely behaviors is their willingness to
return some randomly-chosen result for underspecified queries.)
regards, tom lane
Peter Jackson wrote: > 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 > You might try: SELECT T1.*, T2.ttC, T3.tthD, toD AS sort DISTINCT ON (T1.iId) FROM ... but I think you want to rethink what you're doing. It looks like you want to select against one of the four matching entries in table_three- and I'm not sure which is the right one, or if just any will do. With DISTINCT ON I don't think there is any gaurentee *which* of the four you will get- different environments might get different results. Brian
Regina Thank you that returned the result that the scripts expected. But naturally broke it for mysql. Oh well. Tom To make matters worse I had 'simplified' it a bit. I'm trying to get TikiWiki working nicely with postgres. So far I have basic stuff working now I need to try and get the rest working. Unfortunately my chances of them implementing the changes are slim when it breaks their preferred db. Tom Lane wrote: > Peter Jackson <tasmaniac@iprimus.com.au> writes: >> 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. > > Egad. *Which* row does it return, and why? Anyone reading the SQL > spec would have to say that this query's behavior is undefined: > there are multiple t2 and t3 rows joining to each T1 row, hence > no principled way to decide which ttC and tthD values to output > for a given T1.iId value. > > (One of mysql's more unlovely behaviors is their willingness to > return some randomly-chosen result for underspecified queries.) > > regards, tom lane >
Novice question: How can I apply conditional logic when doing a union of two tables? TableA TableB ------ ------ value1 value2 value1 value2 1 10 5 15 11 20 SELECT * FROM TableA UNION SELECT * FROM TableB; returns: value1 value2 1 10 5 15 11 20 what I want is value1 value2 1 5 5 15 15 20 The tables are huge and already in ascending order.
On Fri, Aug 8, 2008 at 11:13 AM, Wright, George <George.Wright@infimatic.com> wrote: > Novice question: > > How can I apply conditional logic when doing a union of two tables? > > TableA TableB > ------ ------ > value1 value2 value1 value2 > 1 10 5 15 > 11 20 > > SELECT * FROM TableA UNION SELECT * FROM TableB; > returns: > > value1 value2 > 1 10 > 5 15 > 11 20 > > > what I want is > > value1 value2 > 1 5 > 5 15 > 15 20 > > The tables are huge and already in ascending order. Hi, George. What conditional logic are you trying to apply? Union will simply take the rows from the two tables and combine them. Sean
Sean, TableA is non-overlapping contiguous durations of the lowest precedence. TableB is a list of higher precedence durations that need to overlay the records in TableA with any overlap trimmed away from the records in TableA The result is a contiguous non-overlapping combination of the two tables. I would imagine a nested case structure to do the trimming in the select portion, but a join of the two tables would seem to be extremely large and real performance problem. There are thousands of records in the two tables. Thanks. -----Original Message----- From: seandavi@gmail.com [mailto:seandavi@gmail.com] On Behalf Of Sean Davis Sent: Friday, August 08, 2008 11:50 AM To: Wright, George Cc: pgsql Subject: Re: [NOVICE] table unions On Fri, Aug 8, 2008 at 11:13 AM, Wright, George <George.Wright@infimatic.com> wrote: > Novice question: > > How can I apply conditional logic when doing a union of two tables? > > TableA TableB > ------ ------ > value1 value2 value1 value2 > 1 10 5 15 > 11 20 > > SELECT * FROM TableA UNION SELECT * FROM TableB; > returns: > > value1 value2 > 1 10 > 5 15 > 11 20 > > > what I want is > > value1 value2 > 1 5 > 5 15 > 15 20 > > The tables are huge and already in ascending order. Hi, George. What conditional logic are you trying to apply? Union will simply take the rows from the two tables and combine them. Sean
Ok just a bit more info. Unfortunately DISTINCT ON didnt work after all (once I started to get some more data into the tables and try different sorts. Ended up having to select all fields and just use max(field1) etc. The reason I required this (which I didnt realise at the time) is that the gui output can be sorted however you like. (and as DISTINCT ON requires the field to be used as the first sort field it didnt work once you decided to sort by anything but T1.iTd The eventual output is: T1.tOc T2.fId(24) T2.fId(25) T2.fId(26) T2.fId(27) T1.tOd which when you click on the row brings up the rest of the record. so whatever records that relate to t1.tId (which could be 4 or 400) are displayed so the final outcome was SELECT T1.iId,max(T1.tId),max(T1.toC) as status,max(T1.toD),max(T1.toE), max(T2.ttC), max(T3.tthD), max(T1.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 Think I have explained that right. Anyway thanks for you help everyone. Obe, Regina wrote: > > 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 > > >
Peter, Well at least the below you have is more generic and will work for MySQL and pretty much any relational database I can think of. I guess the only issue is that if you use Max you are mixing records (since it returns the max value for each field not the last record value). This may be fine for your purposes, but something to think about. FWIW: There is an easy way to get around the ordering issue of DISTINCT ON and that is to wrap it in a subselect SELECT * FROM (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) As result ORDER BY sort; Hope that helps, Regina -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Peter Jackson Sent: Saturday, August 09, 2008 2:42 AM To: pgsql Subject: Re: [NOVICE] join group by etc Ok just a bit more info. Unfortunately DISTINCT ON didnt work after all (once I started to get some more data into the tables and try different sorts. Ended up having to select all fields and just use max(field1) etc. The reason I required this (which I didnt realise at the time) is that the gui output can be sorted however you like. (and as DISTINCT ON requires the field to be used as the first sort field it didnt work once you decided to sort by anything but T1.iTd The eventual output is: T1.tOc T2.fId(24) T2.fId(25) T2.fId(26) T2.fId(27) T1.tOd which when you click on the row brings up the rest of the record. so whatever records that relate to t1.tId (which could be 4 or 400) are displayed so the final outcome was SELECT T1.iId,max(T1.tId),max(T1.toC) as status,max(T1.toD),max(T1.toE), max(T2.ttC), max(T3.tthD), max(T1.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 Think I have explained that right. Anyway thanks for you help everyone. ----------------------------------------- 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.