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.