Well, Si, I do not know why you got your error and I don't know what
it means. I'm confused what DISTINCT means when fields are NULL. I
would believe NULLs to never be equal to eachother, so that DISTINCT
will only reduce the number of returned rows for rows having values in
all fields. Though I'm talking way over my head and have no idea how
Firebird does this. Hopefully, someone else can enlighten us in this
area.

Looking at your query, I can see that some of your JOINs actually are
inner joins rather than left joins (either that or you want (UTUG.
USER_ID = 1) in your LEFT JOIN rather than in your WHERE clause). Try
changing your query to

SELECT DISTINCT P.ID, P.USERID, U.USERNAME, P.MSG, P.POSTED,
P.LAST_EDITED, P.LAST_EDITED_BY, U1.USERNAME, P.IPADDRESS, P.TOPICID,
U.SHOW_SIG, U.SIGNATURE
FROM DNFBB_POSTS P
JOIN DNFBB_TOPICS T ON (T.ID = P.TOPICID)
JOIN DNFBB_FORUMS F ON (F.ID = T.FORUMID)
JOIN DNFBB_USERGROUPSTOFORUM UGUF ON (UGUF.FORUM_ID = F.ID)
JOIN DNFBB_USERTOUSERGROUPS UTUG
ON (UTUG.USERGROUP_ID = UGUF.USERGROUP_ID)
LEFT JOIN DNFBB_USERS U ON (U.ID = P.USERID)
LEFT JOIN DNFBB_USERS U1 ON (U1.ID = P.LAST_EDITED_BY)
WHERE (P.TOPICID = 1) AND (UTUG.USER_ID = 1);

If there are no NULLS or users that aren't in P.USERID or P.
LAST_EDITED_BY, you may even avoid LEFT JOINs altogether, and if not
an alternative would be to change to subselects for those two
USERNAME-fields.

In my opinion, LEFT [OUTER] JOIN should only be used when there is a
good reason not to use the simpler [INNER] JOIN.

HTH,
Set

--- In firebird-support@yahoogroups.com, "Si Carter" wrote:
> I am trying to run the following sql, which is part of a stored
> proc:
>
> SELECT DISTINCT P.ID, P.USERID, U.USERNAME, P.MSG, P.POSTED,
> P.LAST_EDITED, P.LAST_EDITED_BY, U1.USERNAME,
> P.IPADDRESS, P.TOPICID, U.SHOW_SIG, U.SIGNATURE
> FROM DNFBB_POSTS P
> LEFT JOIN DNFBB_TOPICS T ON (T.ID = P.TOPICID)
> LEFT JOIN DNFBB_USERS U ON (U.ID = P.USERID)
> LEFT JOIN DNFBB_USERS U1 ON (U1.ID = P.LAST_EDITED_BY)
> LEFT JOIN DNFBB_FORUMS F ON (F.ID = T.FORUMID)
> LEFT JOIN DNFBB_USERGROUPSTOFORUM UGUF ON (UGUF.FORUM_ID = F.ID)
> LEFT JOIN DNFBB_USERTOUSERGROUPS UTUG
> ON (UTUG.USERGROUP_ID = UGUF.USERGROUP_ID)
> WHERE (P.TOPICID = 1) AND (UTUG.USER_ID = 1);
>
> And if fails with error:
>
> Message: isc_dsql_prepare failed
>
> SQL Message : -904
> Unsuccessful execution caused by an unavailable resource.
>
> Engine Code : 335544758
> Engine Message :
> sort record size of 69708 bytes is too big
>
> Without the DISTINCT everything is OK.
>
> Does anybody have any ideas on how to solve it? I can provide a db
> script if needed.
>
> rgds
>
> Si Carter