Re: 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.

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(at)postgresql(dot)org
>
> [mailto:pgsql-novice-owner(at)postgresql(dot)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
>
>
>