Unlike Oracle and Microsoft SQL Server 2005, PostgreSQL offers no built-in function to dynamically rank rows. However, one can use the MSDN article, How to dynamically number rows in a SELECT Transact-SQL statement, as a basis for developing techniques that do work in PostgreSQL. This article assumes that there exists a table named
players, containing two columns,
The first technique is the simplest to understand, but requires the use of a temporary sequencer. If sequencer functionality is not available in your database, then this technique cannot be utilitized:
CREATE TEMPORARY SEQUENCE rank_seq;
SELECT player_id, score, nextval('rank_seq') AS rank FROM
(SELECT * FROM players ORDER BY score DESC) as p1
ORDER BY rank
The second technique uses a subselect statement to rank its result. This can be computationally expense because of the
COUNT operation within the subselect statement:
(SELECT COUNT(*) FROM players p2
WHERE p2.score > p1.score) + 1 AS rank
ORDER BY score DESC
The third and final technique uses a self-join operation. It is ideal for environments on lightweight databases where the subselect statement is available. Unfortunately, this technique is also the least intuitive of the three. I recommend running this query piecewise in a query browser to understand how the individual components fit together:
p1.player_id, p1.score, COUNT(p2.player_id) AS rank
players p1, players p2
p2.score >= p1.score
ORDER BY rank
Laurel and I spent this past weekend in the Florida Keys on a group scuba diving trip with Seaventures, leaving Thursday afternoon to board the bus in Alpharetta, and returning early this morning. This was my first diving trip after becoming open water certified.
- The bus arrived at the Ramada Inn at Key Largo on Friday morning. After having breakfast at Doc’s Diner, a local wafflehouse-like establishment within walking distance from the hotel, we geared up and boarded the boat, Rainbow Reef, and left for Molasses Reef.
- The first dive of the day was a disaster. With two to three foot seas, and ocassionally reaching four feet, Titus succumed to sea sickness while in the water. Unable to grab a hold of the drift line, and due to unfortunate under weighting, Laurel and I were dragged by a strong current away from the boat. Laurel then deployed our rescue gear, and we were ultimately picked up by a passing dive boat. Fortunately, I was not the only person to have problems that day. Several inexperienced divers such as myself had to be rescued, while a few student divers never made it off the boat. On the plus side, I did manage to see two nurse sharks while underwater during this brief dive. The second dive of the day was cancelled as a result of inclement weather.
- Saturday morning was met with equally high winds, and the dive was cancelled and moved to that afternoon. One of the crew recalled that the waves were breaking over the side of the boat, with six to eight foot seas. Though a few adventurous souls chose to dive anyway, Laurel and I decided it best to practice our skills in the local pool after yesterday’s events.
- Thus, our second dive of the trip was performed in Three Sisters Reef, where the seas were signifantly calmer and would allow the open water students to begin completing their certification requirements. Unfortunately, visibility was terrible, and the dive would have been basically useless if not for the dive experience acquired. Mike, our instructor, led the dive. He was also the same instructor who left us for dead on Friday afternoon. Just kidding.
- Our third dive at Molases Reef was the best dive of the day. The waters were clear and visibility was excellent. Many divers reported seeing reef sharks, moray eels, and eagle rays.
- Sunday proved to be an excellent day for diving. Our fourth dive of the trip was performed at Spiegel Grove, a Landing Ship Dock that has been sunk to create an artificial reef in Key Largo. The vessel is 510 feet in length, 84 feet wide.
- An alternative dive site at the Benwood Historic Shipwreck was offered to student divers not comfortable with the depths of Spiegel Grove.
- Our last dive of the trip was performed at the French Reef. The location provided a great combination of swim-through caves and a great shallows area with stands of elkhorn coral and a deep reef with large sponges and well developed coral heads. It was an excellent dive to conclude the weekend trip.
The bus left for Atlanta later that evening, and we returned to Atlanta early Monday morning.
I’m happy to announce that I have just accepted a job offer from Enron, and will be moving to Houston, Texas within the next two months. Fortune magazine has named Enron “America’s Most Innovative Company” for six consecutive years. Specifically, I will be working in the accounting department, developing SAP modules for their federal and state tax reporting system, while developing web-based logistics and risk management services for their customer relations department.
It’s true that Enron has received a lot of negative publicity lately. Still, I feel that the company has a bright outlook as the world’s leading electricity and natural gas provider. Though I will miss living in Atlanta, I’m greatly looking forward to this unique and exciting opportunity.