Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Re: Combining single-key sorts to form concatenated-key sort

Re: Combining single-key sorts to form concatenated-key sort

From: Jan Hidders <>
Date: 3 Feb 2001 13:01:53 GMT
Message-ID: <95gvg1$rce$>

Frog2 wrote:
> I'm trying to sort some records. Each record consists of three data
> fields I wish to use as a concatenated key for sorting purposes and
> some detail fields. All three key fields are alphanumeric. All key
> fields in every record will always contain a value (no NULLs).
> Here's the problem: the language I'm using has a sort function but
> that function will only let me sort on *one* field of my choice, not
> all three at once. So how can I achieve a primary - secondary -
> tertiary sort of these records. Presumably there's some scalable way
> to sort/merge each lower key with the key above it (or perhaps the
> reverse, sort from primary key down to lowest-order key).
> What I've come with so far is make a list of each key field's unique
> values unique_p , unique_s, unique_t sorted in the order I want. But
> then how could I use these to extract the records in correct overall
> sorted order?

Your problem description is too vague (what query/programming language are you using? what operators do you have?) to say anything specific about this. But from the looks of it I would say: you can't. Your best bet may be to define a derived column that contains a string that is somehow the concatenation of the three columns, and order on this column.  

> One subtlty - one or more key fields will contain only numbers. So if
> I try an "strcat-'em all-together" solution I'll get bit by the
> "10-sorts-after-1-not-2" problem.

The usual trick is padding the string with zeros so they all have equal length. So 1 becomes "0001", 2 becomes "0002", 10 becomes "0010", 100 becomes "0100", et cetera. I assume there is some maximum for the numbers in that column, and that will tell you how many zeros you need to add.

  Jan Hidders
Received on Sat Feb 03 2001 - 07:01:53 CST

Original text of this message