ORDER BY statements

1) SELECT PK FROM TEST_TABLE;

That obviously works. Now this:

2) SELECT PK, (PK+1) FROM TEST_TABLE;

Also works. However, this currently fails:

3) SELECT PK, (PK+1) FROM TEST_TABLE ORDER BY (PK+1);

While these work:

4) SELECT PK, (PK+1) AS FOO FROM TEST_TABLE ORDER BY FOO;
5) SELECT PK, (PK+1) FROM TEST_TABLE ORDER BY 2;

Turns out the ORDER BY clause requires a column identifier or column index -- you can't put a statement in there.

This has a ramification for WOWebLog project. I have a derived attribute in my EOModel that calculates the "popularity" of all the articles in the database. I then have the database sort on that derived attribute/column to deliver the top 10 most popular articles.

The problem is that EOF is somewhat dumb about derived attributes -- it simply copies over the entered text wherever it's referenced. That means I wind up with SQL like this:

SELECT
  PK,
  CREATION_DATE,
  HIT_COUNT,
  (HIT_COUNT/CURRENT_TIMESTAMP - CREATION_DATE)
FROM
  ENTRY
ORDER BY
  (HIT_COUNT/CURRENT_TIMESTAMP - CREATION_DATE)

Note: I simplified the derived column here for sake of discussion, the real SQL is C_HIT_COUNT/(CAST ((CAST (CURRENT_TIMESTAMP AS TIMESTAMP) - C_CREATION_DATE) AS DECIMAL(12,6))/86400) AS C_HITS_DIVIDED_BY_AGE.)

This results in an error, as does this:

SELECT
  PK,
  CREATION_DATE,
  HIT_COUNT,
  (HIT_COUNT/CURRENT_TIMESTAMP - CREATION_DATE) AS MY_COLUMN
FROM
  ENTRY
ORDER BY
  (HIT_COUNT/CURRENT_TIMESTAMP - CREATION_DATE) AS MY_COLUMN

Right now MySQL and OpenBase allow such statements in ORDER BY clauses (that is, statement #3 above works). This website is deployed on OpenBase, and you can work-around FrontBase's limitation using hand-coded SQL fetchspecs, so it wasn't a greatly pressing issue yet. Still, I filed a feature request with FrontBase in January. Today Geert wrote back saying the feature will be in the next general release of FrontBase 3.x (probably 3.6.11).

Update: Geert says the next release will be 3.6.12

I must say FrontBase and OpenBase have the most responsive folks in the database biz. They're both very customer-driven, which is rather refreshing in today's world.

Sunday, February 23, 2003
12:00 AM