Blog

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?