I have been using various RDBMS systems for a while but now and then I discover things that really annoy me. I just found another one with Oracle. I might be wrong but here is what I found I have to do for a simple task. This article is as much a reference for me as it is a help for you.
Suppose I have a table with lots of entries and I want to get the latest three records. The reason will be that you are only interested in a certain part of the data and want the database to transmit only the data you need…
For example we have a table article with lots of stories and we want the latest three stories for a certain topic to appear on the website. There is no need to transmit any more data than the last three stories from the database to the scripting language or whatever creates your HTML, since this would just load the connection unnecessarily. Here is some sample data:
All we want to get is:
Now assume you use the open source databases MySQL or PostgreSQL all you would need to do is
SELECT id, creation_date, topic FROM article WHERE topic = 'Waterways' ORDER BY creation_date DESC LIMIT 3
Now to do the same in MS SQL Server you would use a similarly simple approach:
SELECT TOP 3 id, creation_date, topic FROM article WHERE topic = 'Waterways' ORDER BY creation_date DESC
And here we go with Oracle. Unfortunately no simple replacement similar to LIMIT or TOP exists
in Oracle. So what do you do?
You can create a view and use ROWNUM:
CREATE VIEW articles_ sorted as SELECT * FROM article WHERE topic = 'Waterways' ORDER BY creation_date DESC SELECT id, creation_date, topic FROM articles_sorted WHERE ROWNUM < 4
If only you would have the rights to create a view. Luckily you can also nest two select statements:
SELECT id, creation_date, topic FROM ( SELECT * FROM article WHERE topic = 'Waterways' ORDER BY creation_date DESC ) WHERE ROWNUM < 4
Funky, isn't it. Guess what, there are even more ways to achieve the same:
SELECT * FROM ( SELECT id, creation_date, topic, RANK() OVER ( PARTITION BY topic ORDER BY creation_date DESC ) ranking FROM article WHERE topic = 'Waterways' ) WHERE ranking <=3
Now I have to admit that these queries work well and are probably more powerful. They also comply to SQL standards. But does it really have to be that painful?