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

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