Search Postgresql Archives

Re: how to do this select?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux