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