Oracle's dense_rank() in PostgreSQL

I'm trying to convert a bit of SQL that I got in the Oracle forum to PostgreSQL.  My primary DBMS is Oracle but I'd like to see this run in PostgreSQL if possible.

Here is my scenario:

--create table xref (
--  combined  varchar(20),
--  memberid     varchar(20)
--insert into xref(combined, memberid) values ('1', '111');
--insert into xref(combined, memberid) values ('1', '222');
--insert into xref(combined, memberid) values ('1', '333');
--insert into xref(combined, memberid) values ('1', '444');
--insert into xref(combined, memberid) values ('2', '555');
--insert into xref(combined, memberid) values ('2', '666');
--insert into xref(combined, memberid) values ('2', '777');
--insert into xref(combined, memberid) values ('2', '888');
--insert into xref(combined, memberid) values ('2', '999');
--insert into xref(combined, memberid) values ('3', '987');

SELECT newid, oldid
  FROM (
         SELECT memberid oldid,
                last_value(memberid) over (
                                         PARTITION BY combined ORDER BY memberid
                                         ROWS BETWEEN CURRENT ROW AND unbounded
                                        ) newid
           FROM xref

--NEWID         OLDID
--------------- -------------
--444           111
--444           222
--444           333
--444           444
--999           555
--999           666
--999           777
--999           888
--999           999
--987           987
--10 rows selected

Any ideas?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

earth man2Commented:
You're a born optimist !  This level of analytic functionality is not currently built into Postgresql.  That's what you pay your money for !

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cmulcahyAuthor Commented:
Of course I'm an optimist.  If I were to face reality all the time, I am not sure I could get out of bed each day.

Is there a way to produce the output I'm after without dense_rank()?  I want to take the input shown, of all of the similar COMBINED fields, take the alphabetically last one as the NEWID and use all of the values like it as OLDID?

I'm thinking subselects.  Now that I think about it a bit more, I think I may have the answer but will need to do some testing.  I still welcome answers here!

cmulcahyAuthor Commented:
I would still like to get dense_rank()-like functionality out of PostgreSQL, but..

select sNew.newMemberID, xref.memberid
  from xref, (select combined, max(memberid) as newMemberID
                   from xref
                  group by combined) sNew
 where xref.combined = sNew.combined;

This produces the output achieved with dense_rank() in Oracle, albeit a bit less efficiently, which is a bit of a problem with my 14 million row data set but at least it produces that output.

If there were an add-on module or plpgsql script that would produce the output I get from dense_rank(), I would be a very happy guy.

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

What exactly does dense_rank() do? FWIW, you can build your own aggregate functions using PL/pgSQL and CREATE AGGREGATE. Is that what you're looking for?
earth man2Commented:
You can improve performance for this type of problem using set returning functions.   The programming goes on behind the stored procedure interface instead of within the SQL statement.
cmulcahyAuthor Commented:
rank() and dense_rank() in Oracle assign a ranking to each row in a result set:

Given the data (arbitrarily chosen):
  Keyid     Value
  1            Blue
  9            Green
  2            Apple

select dense_rank(), value, keyid from the_data order by value;

  1     Apple     2
  2     Blue       1
  3     Green    9

It just numbers, sequentially, the rows in the result set.  If I had two Blue values in the result set, it would have returned 2  Blue twice.  It will duplicate the rank for duplicated values.

rank() does much the same thing, except when it encounters duplicates, it will assign the second value the same rank as the first, but skip the rank that it would have had.  For example, rank 2 would have been duplicated but 3 would have been skipped.


I'm a decent C programmer, though I'm much better with C++.  Anyone care to at least get me started writing a function?  Of course, a plpgsql function is always welcome!

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.