Sorting an array

[Dylan Parry]

Hi folks,

I have a database that contains records with IDs like "H1, H2, H3, ...,
Hn" and these refer to local government policy numbers. For example, H1
might be "Housing Policy 1" and so on. For some more insight, not all
policies will be prefixed with an "H", an in fact they could be prefixed
with *any* letter combination, but they will always end with a number.

When I retrieve them from the database I use "ORDER BY id" to get them
into a sensible order, but obviously as the IDs are strings I get them
returned like "H1, H10, H11, H2, ...". Alphabetically this is fine, but
logically (to a human at least) this order makes no sense whatsoever.

So what I'm looking for is either a way of changing the order in SQL to
something more sensible like "H1, H2, H3, ..., H10, H11" or a method of
sorting the results array *after* it has been returned from my SQL
query.

Any ideas?

 

[Dylan Parry]

When I retrieve them from the database I use "ORDER BY id" to get them
into a sensible order, but obviously as the IDs are strings I get them
returned like "H1, H10, H11, H2, ...". Alphabetically this is fine, but
logically (to a human at least) this order makes no sense whatsoever.

I should add that it's a multidimensional array that requires sorting,
where each row is a unique record from the database.

 

[TheSteph]

You can split your field in 2 Fields, and concatenate them in your
listview ...

- one field "ID_PREFIX" with the letter ("H",...)
- a second field "ID_NUMBER" with the numbers (1,2,...)

 

[Dylan Parry]

You can split your field in 2 Fields, and concatenate them in your
listview ...

Not really an option as the database has existed for some time already
and there are many thousand records that would need processing...

 

[Dylan Parry]

Any ideas?

Sorted (so to say). I eventually implemented IComparable for the objects
I was trying to sort rather than try to sort them when they come out of
the database. That way I was able to use regular expressions to split up
the string and integer components of the ID and compare them
individually.

So I first compared the string parts and then, if the comparison
returned 0, compared the integers. Eg:

A1 compared to B1
A is before B, so return -1
return -1

A2 compared to A1
A is the same as B, so return 0,
2 is after 1, so return 1
return 1

And so on.

 

[Guest]

If you always have one character you can do something create a separate
column of the number part, cast it to an integer and sort on the integer.
Untested code
CAST(SUBSTRING(TargetColumn,1) as Int) AS SortColumn.....ORDER BY SortColumn

 

[Kevin Spencer]

A database operation doesn't return an array. So, what exactly is it that
you want to sort?

In fact, you can almost always do your sorting in the query itself. But your
requirements are not clear. You say that the ID is stored as a string, but
that you want to sort by ID (for some unstated reason). You also say that
the order that is returned should be "sensible," but do not define what you
mean by "sensible." In fact, it could well be argued that alphabetical
sorting *is* "sensible" for IDs that are stored as text. You seem to
indicate that somehow the characters should be stripped from the IDs to sort
them as if they were numbers. If there are other starting characters, do the
numbers that follow them duplicate across different ID character/number
combinations? Apparently, the character(s) means something, but we don't
know what. For example, consider the following:

H1, H2, H11, A1, A10, A11, F2, F12, B1, B10

Now, if you were to sort them by ignoring the beginning character, you would
get:

H1, A1, B1, H2, F2, A10, B10, H11, A11, F12

Is *that* "sensible?"

So, could you please be more specific, both about what sort of rules you
want to apply to the sorting, how you define "sensible," and what the type
of the object is that you're sorting?

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Chicken Salad Alchemist

What You Seek Is What You Get.

 

[Chris Dunaway]

So, could you please be more specific, both about what sort of rules you
want to apply to the sorting, how you define "sensible," and what the type
of the object is that you're sorting?

I think he was pretty clear. My interpretation is that he wants the
id's sorted first by the alphabetic prefix character(s) and then
numerically so that:

H1, H2, H11, A1, A10, A2, A11, F2, F12, B1, B10

becomes

A1, A2, A10, A11, B1, B10, F2, F12, H1, H2, H11

If the items are in an array or arraylist, then a class that implements
IComparer or IComparable would probably be the simplest solution.

 

[Dylan Parry]

I think he was pretty clear. My interpretation is that he wants the
id's sorted first by the alphabetic prefix character(s) and then
numerically so that:

H1, H2, H11, A1, A10, A2, A11, F2, F12, B1, B10

becomes

A1, A2, A10, A11, B1, B10, F2, F12, H1, H2, H11

Yes, that was precisely what I was attempting to do.

If the items are in an array or arraylist, then a class that implements
IComparer or IComparable would probably be the simplest solution.

And that is exactly how I achieved it

 

[sloan]

I have a "multi valued IComparer" on my blog site.

http://sholliday.spaces.msn.com/ 6/19/2006 entry

The issue I think is "breaking the tie". Which my blog entry addresses.

You should be able to start with it, to get what you need.