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
> 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.
In response to
pgsql-novice by date
|Next:||From: Tom Lane||Date: 2008-08-08 13:57:32|
|Subject: Re: join group by etc |
|Previous:||From: Obe, Regina||Date: 2008-08-08 13:53:57|
|Subject: Re: join group by etc|