Thread: order by, but eliminating dupes

So lets say I got a table A(x,y,z, Q) ordered by column Q, with contents
like so:

x      y      z    Q
------------------------
21    5      x     1
21    5      x     2
43   10      t     3
1     2       a     5
43   10      t     10
21    5      x     50

what I would like is to take the results of this query, and maintaining
the order, only list x, y, z , AND
not list any duplicates.

So the result I'd want after getting this query would be:

x     y     z
----------------
21    5    x
43   10    t
1      2    a

So I'm pulling the results as they come along, and ignore any subsequent
dupes.

I don't know if this is even possible. I've tried combinations of SELECT
DISTINCT, GROUP BY, and others
with
no luck. The best I could do was to do the order by in a subquery, then
do a SELECT UNIQUE on the subquery.
But that kills the order of the subquery. I've tried group by x,y,z but
then I can't ORDER BY Q.

- L

On Wed, Sep 03, 2003 at 22:52:44 -0400,
  LH <_pgsql-novice_@geekhouse.no-ip.com> wrote:
> So lets say I got a table A(x,y,z, Q) ordered by column Q, with contents
> like so:
>
> But that kills the order of the subquery. I've tried group by x,y,z but
> then I can't ORDER BY Q.

I think you can do something like this:

select x, y, z from
  (select distinct on (x, y, z) x, y, z, q order by x, y, z, q)
  order by q;

Something like this works:
select a,b,c from t1 group by a,b,c order by min(q)

Jeff
On Wednesday, September 3, 2003, at 09:52  PM, LH wrote:

> So lets say I got a table A(x,y,z, Q) ordered by column Q, with
> contents
> like so:
>
> x      y      z    Q
> ------------------------
> 21    5      x     1
> 21    5      x     2
> 43   10      t     3
> 1     2       a     5
> 43   10      t     10
> 21    5      x     50
>
> what I would like is to take the results of this query, and maintaining
> the order, only list x, y, z , AND
> not list any duplicates.
>
> So the result I'd want after getting this query would be:
>
> x     y     z
> ----------------
> 21    5    x
> 43   10    t
> 1      2    a
>
> So I'm pulling the results as they come along, and ignore any
> subsequent
> dupes.
>
> I don't know if this is even possible. I've tried combinations of
> SELECT
> DISTINCT, GROUP BY, and others
> with
> no luck. The best I could do was to do the order by in a subquery, then
> do a SELECT UNIQUE on the subquery.
> But that kills the order of the subquery. I've tried group by x,y,z but
> then I can't ORDER BY Q.
>
> - L
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster