Sunday, July 29, 2007

Simulating LIMIT with offsets in SQL

How can you do this (x is number of rows to fetch, y is offset):
SELECT * FROM my_table LIMIT x, y
(Mysql)

or this:
SELECT * FROM my_table LIMIT x OFFSET y
(PostgreSQL)

in a DBMS that doesn't support LIMIT statements (take Oracle for example).

In Oracle, you can use ROWNUM, which holds the current row number:
SELECT * FROM
(SELECT ROWNUM limit, * FROM my_table ORDER BY order_field)
WHERE limit BETWEEN x AND y
In SQL Server (>2005), you can use a similar trick, using the ROW_NUMBER function:
WITH ordered AS (
SELECT ROW_NUMBER() OVER (ORDER BY order_field) AS limit, *
FROM my_table)
SELECT *
FROM ordered
WHERE limit BETWEEN x AND y
When using another database (Access for example), you can use TOP when available:
SELECT * FROM
(SELECT TOP x * FROM
(SELECT TOP x+y * FROM my_table ORDER BY order_field)
AS innerT
ORDER BY order_field DESC) AS outerT
ORDER BY order_field
Or, another option:
SELECT TOP x * FROM my_table
WHERE order_field NOT IN
(SELECT TOP y * FROM my_table ORDER BY order_field)
Some databases don't allow TOP in subselects, so we have to use yet another method:
SELECT * FROM my_table outerT
WHERE
(SELECT COUNT(*)
FROM my_table innerT
WHERE innerT.order_field <= outerT.order_field)
BETWEEN y+1 AND x+y
ORDER BY outerT.order_field ASC;
Should that fail too, you can always do:
SELECT TOP x * FROM my_table WHERE order_field > z
with z the last fetched order_field value from the previous page.

You can also find a handy article on the IBM site describing how you can simulate row numbers.

Friday, July 27, 2007

Game: Jade Empire - Special Edition

So I picked up Jade Empire for the PC a few days ago. I was astonished because I didn't even know it was released for the PC. (I've been jealous of console owners who could play the game and then I even miss the PC release!)

So after I quick install I was ready to go. I was more than happy to experience a new Bioware-experience. As coincidence would have it, I had been playing a few Neverwinter Nights premium modules the days before just because I wanted a Bioware experience. (And no, KotOR 2 and NWN 2 just aren't the same.)

And the game didn't disappoint. I you haven't played it yet: you should. The whole story was fantastic. The characters were well though out, and the combat was fun without being frustrating (I didn't die a single time on the easiest difficulty level).

If you are a hardcore PC RPG gamer you will notice that this game is a tad linear. Of course there are quite a lot of side quests but you can't roam very freely. This seems to be a typical characteristic of more 'modern' RPGs, like Fable, or even Overlord. On the other hand, this also has pros: at least you don't get lost or distracted. Also: Fable could learn a few things from Jade Empire, this is how a console RPG should be done: it's all about the story and the atmosphere. The game is fun, quick to pick up and you don't have to manage your inventory and character every two steps while still keeping enough numbers, statistics and options for you to look at.

I think I'm going to rest for a bit now after this marathon-gaming-session. But I think I'm going to replay the game with another character later.

Let's hope that Mass Effect comes to the PC as well.

Pros:
  • Another Bioware classic.
  • Amazing atmosphere.
  • Great story.
  • Cool styles and techniques.
  • Original oriental setting of the game.
  • A right amount of side quests.
  • Well ported: no crashes, no control problems.
  • Graphics are still good.
  • Music.
Cons:
  • Camera misbehaves sometimes .
  • Leaves you wanting much more ;).

Tuesday, July 03, 2007

Game: Overlord

This game took me a little longer to finish: two days.

The game itself is actually pretty good but not that special. I had expected a bit more from it: being more evil for example.

Pros:
  • Nice story.
  • Minions.
  • Graphics are decent.
  • Sound is nice.
  • Maps and environments are awesome.
  • Will remind you of Dungeon Keeper (and Fable too).
Cons:
  • Not evil enough.
  • Very, very linear. No replay value, apart from the different endings (which you can watch anyway by installing RADTools).
  • Could do with a bit more spells.
  • Just needs more tweaking, Overlord 2 could be something really worth your while tho, so why not pick up this one?