Re: Using Generic Comparator in Oracle ORDER BY

From: <casey.kirkpatrick_at_gmail.com>
Date: 26 Oct 2005 12:23:01 -0700
Message-ID: <1130354581.465450.66590@g49g2000cwa.googlegroups.com>

Hans,

I know I can write a function that converts my column to something that can be ordered lexicographically (and can thus be sorted with ORDER BY) - I've known that from post1. The problem is no such function could handle an input of arbitrary depth. What I was looking for was a tool that allows me to tell Oracle, "hey, order by column X, but instead of using the standard lexicographic or numeric ordering, use my custom comparison function F".

To put it another way:

---

Your Solution:

---

SELECT C1
FROM TABLE
ORDER BY F(C1) In your first example, F maps from (u.name, u.type, u.line) to a string <name||type||to_char(line)> which must be lexicographically sorted.

In your second example, your function maps from (e.partno) to a string <description>, which must be lexicographically ordered.

The fact is, with Oracle's ORDER BY, I must convert C1 values into strings and the order by those strings lexicographically (or convert C1 into numbers and order those numerically). That's not what I want.

---

My Desired Solution:

---

SELECT C1
FROM TABLE
ORDER BY C1
COMPARING WITH F This should order all values in C1 directly, using F to establish which values in C1 are "less than" other values in C1 and ordering appropriately (you see - *NO* lexicographic or numeric ordering).

In my desired solution, F would take any two arguments from C1, and tell Oracle which of them is less than the other. Oracle would then use the function F to sort C1 directly... using no LEXICOGRAPHIC or NUMERIC comparisons whatsoever.

---

---

Do you see the difference? And my question still stands: does anyone know of a way to do this in Oracle 91 or 10g? Received on Wed Oct 26 2005 - 14:23:01 CDT