Re: how to do this select?

Yi Zhao wrote:
> ok, thanks, I will create a new message when I post next time.

And it's nice to reply below the original message, after cutting off the
bits that don't matter anymore. It saves space and makes your messages
easier for other people to read, which means you are more likely to get
replies and people are more likely to help you out.

> About my question, I think distinct [on] can't solve my problem, because I
> want to get more than one rows. if there is more  than (or equal) 2 (eg:
> 2, 3, 4, 100 ...)rows have the same value of column 'b' , I want to get
> only 2 rows. if lesse than 2, I want get all the result of them.
>
> ps: I' think, the *2* in my example is not appropriate, how about 10,
> 50?

OK, so for each distinct value in `b' you wish to obtain a random
selection of `n' or fewer rows in which that value of `b' occurs?

I'm pretty sure you want the PostgreSQL 8.4 windowing functions. See:

http://www.depesz.com/index.php/2009/01/21/waiting-for-84-window-functions/
http://developer.postgresql.org/pgdocs/postgres/tutorial-window.html
http://developer.postgresql.org/pgdocs/postgres/functions-window.html

Is this a production system, or something in development? If it's only
in development I strongly suggest moving to PostgreSQL 8.4 now, so that
by the time you're ready to release 8.4 will have been released too and
you will be able to use its features.

If that's not an option ... there are probably clever ways of doing what
you want, but personally I'd just hack something ugly in Pl/PgSQL like
(using my previous example code):

CREATE OR REPLACE FUNCTION blah () RETURNS SETOF ab AS
$$
DECLARE
   thisb text;
BEGIN
   FOR thisb IN SELECT DISTINCT(b) FROM ab LOOP
     RETURN QUERY SELECT a, b FROM ab WHERE b = thisb LIMIT 2;
   END LOOP;
   RETURN;
END;
$$ LANGUAGE 'plpgsql' STABLE;

test=# SELECT * FROM blah();
  a | b
---+---
  X | A
  Y | A
  X | B
  D | B
  P | C
(5 rows)

--
Craig Ringer