Re: join group by etc

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.