Thread: Q: select query

I am trying to write a query to select some rows.

My data consists of rows like this
  1       a  1       b  1       c  2       d  3       e  3       f  ...

I would like to select the second and subsequent rows where the first
column is the same:
  1       b  1       c  3       f

in other words, all but the first row of a group.

Any ideas?

Thanks, Ralph

> in other words, all but the first row of a group.

Interesting question. The below should work and be quick so long as
there is a UNIQUE(col1, col2) constraint.

SELECT col1    , col2 FROM jWHERE col2 != (SELECT col2                 FROM j AS jsub                WHERE col1 =
j.col1            ORDER BY col2 ASC                LIMIT 1); 

On 12 Sep 2003 10:58:45 -0700, grk@usa.net (G. Ralph Kuntz, MD) wrote:
>I would like to select the second and subsequent rows where the first
>column is the same:
>
>   1       b
>   1       c
>   3       f
>
>in other words, all but the first row of a group.

all =    SELECT * FROM t;

but =    EXCEPT

the first row of a group =SELECT i, min(x) FROM t GROUP BY i;

or (if there are more columns)SELECT DISTINCT ON(i) * FROM t ORDER BY i, x;

Putting it together:
SELECT i, x FROM t EXCEPT (SELECT i, min(x) FROM t GROUP BY i);

or
SELECT * FROM t EXCEPT (SELECT DISTINCT ON(i) ...);

ServusManfred