Ask Your Question
0

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

asked 2014-10-30 11:05:42 +0200

Dycius gravatar image

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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-05 18:15:06.044579

1 Answer

Sort by » oldest newest most voted
0

answered 2014-12-14 17:17:35 +0200

Alex Thurgood gravatar image

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.

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2014-10-30 11:05:42 +0200

Seen: 45 times

Last updated: Oct 30 '14