Re: 'distinct on' and 'order by' conflicts of interest

On Fri, Dec 31, 2004 at 15:02:56 -0600,
stephen(at)thunkit(dot)com wrote:
>
> I've put an '*' next to the rows I want. So my dilemma is two part.
> First, I want to sort by the ordinal information only when the arc is
> pointing from the source object (id 638) to the other objects. Well, it's
> pretty easy to determine which arcs are pointing the right way with this
> addition:
>
> select nodes.title, nodes.name, nodes.id, arcs.ordinal, CASE WHEN
> nodes.id=arcstart THEN '1' ELSE '0' END as direction from Nodes,Arcs where
> (Arcs.ArcEnd=Nodes.id and Arcs.ArcStart in ('638') and Arcs.Type=
> 'contains') or (Arcs.ArcStart=Nodes.id and Arcs.ArcEnd in ('638') and
> Arcs.type = 'contained_by' ) order by direction, arcs.ordinal

You want to use this ordering to do the distinct and make it a subselect
so that you get the output order you want.

Something like:
SELECT
title, name, id, ordinal, direction
FROM
(SELECT
DISTINCT ON (nodes.id)
nodes.title, nodes.name, nodes.id, arcs.ordinal,
CASE WHEN nodes.id=arcstart THEN '1' ELSE '0' END as direction
FROM Nodes, Arcs
WHERE
(Arcs.ArcEnd=Nodes.id
AND Arcs.ArcStart in ('638')
AND Arcs.Type= 'contains')
OR
(Arcs.ArcStart=Nodes.id
AND Arcs.ArcEnd in ('638')
AND Arcs.type = 'contained_by')
ORDER BY nodes.id, direction, arcs.ordinal
) AS a
ORDER BY ordinal