Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

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

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.