LO Base: Return two records at a time in a sorted query

[Dycius]

Hi all,

I will use lots of table examples to describe this one.

I want to return a record and the next record at once in a sorted by ascending way. I have my “Fruits” table in a testing database with the following to describe this:

id  |  SomeText
0   |  Orange
1   |  Banana
2   |  Apple
3   |  Pear
4   |  Kiwi

However, it needs to be sorted in ascending order:

id  |  SomeText
2   |  Apple
1   |  Banana
4   |  Kiwi
0   |  Orange
3   |  Pear

Next, I want to display two records side by side, so I tried writing this:
SELECT “p”.“id”, “p”.“SomeText”, “n”.“id” AS “NextID”, “n”.“SomeText” AS “NextSomeText” FROM “Fruits” AS “n”, “Fruits” AS “p” WHERE “n”.“id” = “p”.“id” + 1

Which displays this:

id  |  SomeText  |  NextID  |  NextSomeText
0   |  Orange    |  1       |  Banana
1   |  Banana    |  2       |  Apple
2   |  Apple     |  3       |  Pear
3   |  Pear      |  4       |  Kiwi

When I apply sorting to that, I get the next ID number instead of the next sorted fruit.

However, the final results that I want need to look something like this:

id  |  SomeText  |  NextID  |  NextSomeText
2   |  Apple     |  1       |  Banana
4   |  Kiwi      |  0       |  Orange
3   |  Pear      |  NULL    |  NULL

I hope this makes some type of sense.

Thanks.

[iplaw67]

Craft your SQl so that it compares the value from a given value in SomeText and then select the next alphabetically ordered value from the result from NextSomeText ? That’ll take more than a simple sort to achieve. This is not a LO specific problem, but a SQL problem - better off asking on a SQL specific forum.