Re: join group by etc

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