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

On Fri, Dec 31, 2004 at 15:02:56 -0600,
  stephen@thunkit.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