X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2321E52A55 for ; Tue, 22 Mar 2005 02:53:00 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 94862-06 for ; Tue, 22 Mar 2005 02:52:50 +0000 (GMT) Received: from biglumber.com (biglumber.com [207.228.252.42]) by svr1.postgresql.org (Postfix) with SMTP id EBC405671B for ; Tue, 22 Mar 2005 02:52:49 +0000 (GMT) Received: (qmail 28169 invoked from network); 22 Mar 2005 02:52:53 -0000 Received: from unknown (HELO localhost) (207.228.252.42) by 0 with SMTP; 22 Mar 2005 02:52:53 -0000 From: "Greg Sabino Mullane" To: pgsql-sql@postgresql.org Subject: Re: equivalent of oracle rank() in postgres X-PGP-Key: 2529 DF6A B8F7 9407 E944 45B4 BC9B 9067 1496 4AC8 X-Request-PGP: http://www.biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 In-Reply-To: <2EB549AAAFEA264FA926269A5BD5E9EB14657F@certsrv.satyam.com> Date: Tue, 22 Mar 2005 02:52:53 -0000 X-Mailer: JoyMail 1.48 Message-ID: <31bf0e84213104473bc7c80d2b0a9fb3@biglumber.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.255 tagged_above=0 required=5 tests=AWL, UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200503/295 X-Sequence-Number: 20774 -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > Jus wanted the equivalent for rank() as in tis example.. > SELECT * > FROM ( > SELECT employee_id, last_name, salary, > RANK() OVER (ORDER BY salary DESC) EMPRANK > FROM employees) > WHERE emprank = 3; There is no direct equivalent to rank(), but there are certainly other ways to get the results. The above query can be written in PostgreSQL as: SELECT employee_id, last_name, salary FROM employees WHERE salary = (SELECT DISTINCT salary FROM employees ORDER BY salary DESC OFFSET 2 LIMIT 1); - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200503212152 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iD8DBQFCP4hwvJuQZxSWSsgRAoKPAKDE0pB4NueE0Dh9EfJiXw79SvCDoACcC4xb ydxVgK9DgGHQXJqFIrlHIIo= =GRIX -----END PGP SIGNATURE-----