pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Q: select query
3+ messages / 3 participants
[nested] [flat]

* Q: select query
@ 2003-09-12 17:58  G. Ralph Kuntz, MD <grk@usa.net>
  0 siblings, 2 replies; 3+ messages in thread

From: grk@usa.net @ 2003-09-12 17:58 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Q: select query
@ 2003-09-13 18:17  Rod Taylor <rbt@rbt.ca>
  parent: G. Ralph Kuntz, MD <grk@usa.net>
  1 sibling, 0 replies; 3+ messages in thread

From: Rod Taylor @ 2003-09-13 18:17 UTC (permalink / raw)
  To: G. Ralph Kuntz, MD <grk@usa.net>; +Cc: pgsql-sql

> 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 j
 WHERE col2 != (SELECT col2
                  FROM j AS jsub
                 WHERE col1 = j.col1
              ORDER BY col2 ASC
                 LIMIT 1);

Attachments:

  signature.asc (application/pgp-signature, 186B) download

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Q: select query
@ 2003-09-13 18:52  Manfred Koizar <mkoi-pg@aon.at>
  parent: G. Ralph Kuntz, MD <grk@usa.net>
  1 sibling, 0 replies; 3+ messages in thread

From: Manfred Koizar @ 2003-09-13 18:52 UTC (permalink / raw)
  To: grk@usa.net; +Cc: pgsql-sql

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) ...);

Servus
 Manfred



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2003-09-13 18:52 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-12 17:58 Q: select query G. Ralph Kuntz, MD <grk@usa.net>
2003-09-13 18:17 ` Rod Taylor <rbt@rbt.ca>
2003-09-13 18:52 ` Manfred Koizar <mkoi-pg@aon.at>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox