Dear newsgroups readers,

I am trying to imitate the MSSQL TOP function in ElevateDB. I came across the RANGE clause which seems like it would do it but I am not able to use it correctly.

The SQL statement

SELECT id FROM DATA ORDER BY id DESC

returns 3 rows with values 3, 2 and 1. If I include the RANGE clause and modify the SQL statement into

SELECT id FROM DATA ORDER BY id DESC RANGE 1 TO 1

it then returns only 1 row, which is correct, but the value is unexpectedly 1 when it should be 3 (as far as I understand the RANGE clause behaviour). Furthermore, if I execute the SQL statement

SELECT id FROM DATA ORDER BY id DESC RANGE 2 TO 3

twice, the first time it returns 2 rows with values 2 and 1, the second time it returns 3 rows with values 3, 2 and 1.

Could someone explain this strange behaviour or at least shed some light on how RANGE works?

I am using the ElevateDB Manager (Unicode).


Bojan

Bojan,

<< I am trying to imitate the MSSQL TOP function in ElevateDB. I came across
the RANGE clause which seems like it would do it but I am not able to use it
correctly.

The SQL statement

SELECT id FROM DATA ORDER BY id DESC

returns 3 rows with values 3, 2 and 1. If I include the RANGE clause and
modify the SQL statement into

SELECT id FROM DATA ORDER BY id DESC RANGE 1 TO 1

it then returns only 1 row, which is correct, but the value is unexpectedly
1 when it should be 3 (as far as I understand the RANGE clause behaviour).
>>

Yes, there is an issue with the RANGE clause when combined with the ORDER
BY.  A fix will be available by this weekend.

--
Tim Young
Elevate Software
www.elevatesoft.com