Re: Re: Restriction by grouping problem.

At 17:19 27/07/01 -0700, Josh Berkus wrote:
>Phillip,
>
>> What it effectively does is executes the second SELECT for each row
>> of the
>> first select, a bit like a column-select expression, but it allows
>> access
>> to all rows.
>
>Ah! I knew that there had to be a simple way to do what you want to do
>in PGSQL:

The first two examples I sent do it 'simply' in PG. Unfortunately the
example you give will only return one row, and since Jeff wanted one
gettime for each SID, we'd need to modify both your and my first solution to:

Select SID,Min(GETFILE) From
(Select Min(DATETIME),SID From LOGS Group by SID) as MDT,
LOGS L
Where
L.SID = MDT.SID
And L.DATETIME = MDT.DATETIME

This will work, but will not solve the larger problem of 'give me all the
attrs of of the record with the least datetime for a given SID'. Jeff does
not need this, but it is a common problem. Simply using min/max works for
one attr, but fails to provide a consistent view of multiple attrs. PG has
no elegant single-statement solution to this problem. The SQL solution I
use in other systems is, as I mentioned earlier:

Select
[any list of attrs from the 'best' record]
From
(Select Distinct SID From LOGS) as S, -- Get the grouping attrs
(Select * From LOGS L Where L.SID = S.SID -- For each group do a
'row-select'
Order By DATETIME Asc Limit 1) as MDT

The second select can be thought of as analagous to a
column-select-expression, but allowing access to all attrs of the resulting
row.

If you want f1,f2,and f3 from LOGS, then a similar result would be achieved
(inelegantly) by:

Select
(Select F1 From LOGS L Where L.SID = S.SID
Order By DATETIME Asc Limit 1) as F1,
(Select F2 From LOGS L Where L.SID = S.SID
Order By DATETIME Asc Limit 1) as F2,
(Select F3 From LOGS L Where L.SID = S.SID
Order By DATETIME Asc Limit 1) as F3
From
(Select Distinct SID From LOGS) as S, -- Get the grouping attrs

Assuming DATETIME is unique then F1, F2, F3 will all come from the same row
and you will have a consistent record.

I have not checked, but I'd guess that PG will create a cross block with
four entries, whereas the original syntax above should just use 2 entries.
Like I said, it's just an optimizer hint.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/