Re: how to do this select?

Yi Zhao wrote:
> thanks Ringer.
> my mean is that:
> I want less than 2 rows which have the same value of column "b"!
>
> for example, there is 3 columns have the same value "A",
>  X | A
>  X | A
>  Y | A
> I want my result have two of them.

Less than two? ie just one? That's easy:

test=# SELECT DISTINCT ON (b) a, b FROM ab;
  a | b
---+---
  Y | A
  D | B
  P | C
(3 rows)

but if you want a randomly selected TWO rows, rather than just one, it's
harder. In PostgreSQL 8.4 (not yet released) you could use the new
windowing functions to do it quite easily. I can't off the top of my
head think of any reasonably nice way to do it in 8.3.

--
Craig Ringer