Ordering an alphanumeric resultset.

[oracle_faq]

Hi,

I have a resultset which looks like below.

A
B
C
D
E
F
G
H


I would like to order this result set with reference to another string by the dictinary distance. So taking D as reference, I would like to see the following output

D
C
E
A
F
G
H

This is a simple example. Actually the resultset I am retreiving can have variable length string. So the solution should be scalable to

Thanks in advance.

[jmodic]

I'm totaly confused. Can you explain a bit further the logic behind the wanted sorted result? Your example doesn't make any sense to me. And btw, where have letter 'B' dissapeared to in your sorted result?

[oracle_faq]

The letter B should be there in the result set. Sorry about that.

Here is the problem I am trying to solve. I have a table called TASKS.

TASK, LOCATION, PRIORITY

The data in it looks something like this.
TASK1 112A1 1
TASK2 112B1 1
TASK3 223C2 1
TASK4 334D4 1
TASK5 445E5 1
TASK6 113A1 2
TASK7 223D3 2
TASK8 446E7 2

If a user is dong TASK4, he will be at Location 334D4. After completion of TASK4, I want to display the next task based on Priority and the location closest to him. In this case it would be either 223C2 or 445E5.

The reason for doing something like this is that the user is already in a particular location, so give him his next task in the vicinity only.

It has to be done in one SQL only. Since I am changigna third party software, so I can afford to only change the SQL taht fetches the next task. Beyond that I don't know the processing logic to make any procedural changes.

Thanks.

[DaPi]

You should be able to devise a numerical metric lbased on something like:
10000*to_number(substr(location,1,3)) + 10*ascii(substr(location,4,1)) + to_number(substr(location,5,1))

BUT the details depend on how those crummy codes are to be interpreted:
If you're at 445E5, which is nearer 446E5 or 445F5?

P.S. better solution (but not just one SQL): create a lookup table to convert the codes to (x,y) coordinates and create a function to determine the distance between to location codes.

[jmodic]

Now, after your explanation, it makes perfect sense.

I would use LEAD() and LAG() analytical functions. As I understand, for each task you basicaly need to display the closest two tasks (one sorted before it and one after it) with respect to priority and location. So, does the following gives you what you need?

SELECT task, location, priority,
       lead(task) OVER(ORDER BY priority, location) next_task_a,
       lead(location) OVER(ORDER BY priority, location) next_location_a,
       lead(priority) OVER(ORDER BY priority, location) next_priority_a,
       lag(task) OVER(ORDER BY priority, location) next_task_b,
       lag(location) OVER(ORDER BY priority, location) next_location_b,
       lag(priority) OVER(ORDER BY priority, location) next_priority_b
FROM tasks;

[oracle_faq]

Thanks jmodic and DaPi. I will try both the solutions and see which one works out with minimal code changes.