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 - http://webpageworkshop.co.uk
A Flower? 9 2585
Dylan Parry wrote:
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.
--
Dylan Parry - http://webpageworkshop.co.uk
A Flower?
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" <us****@dylanparry.comwrote in message
news:vs**************@dylanparry.com...
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 - http://webpageworkshop.co.uk
A Flower?
TheSteph wrote:
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 - http://electricfreedom.org
A Flower?
Dylan Parry wrote:
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.
--
Dylan Parry - http://webpageworkshop.co.uk
A Flower?
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
"Dylan Parry" wrote:
TheSteph wrote:
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 - http://electricfreedom.org
A Flower?
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.
"Dylan Parry" <us****@dylanparry.comwrote in message
news:vs**************@dylanparry.com...
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 - http://webpageworkshop.co.uk
A Flower?
Kevin Spencer wrote:
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.
Chris Dunaway wrote:
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 ;)
--
Dylan Parry - http://electricfreedom.org
A Flower?
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.
"Dylan Parry" <us****@dylanparry.comwrote in message
news:vs**************@dylanparry.com...
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 - http://webpageworkshop.co.uk
A Flower?
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Allie |
last post by:
How would I go about sorting this structure by title?
typedef struct {
char author;
char title;
char code;
int hold;
int loan;
} LIBRARY;
|
by: Andrew Poulos |
last post by:
Say I have two arrays with the same number of elements that are related:
var a1 = ;
var b2 = ;
I need to sort a1 but have the order of the elements in b2 reflect the
any new order in a1. So if a1 sorts as
then b2 becomes
|
by: Larry |
last post by:
Thanks for the help in advance. Does anyone have a code sample for the
following scenario.
how would you sort the array of books (BookView) by author and by price.
if there were a number of items in BookView Array?
Public BookView(0) As Book
Class Book
Public author As String
|
by: VooDoo |
last post by:
Hi,
I am getting confused with sorting arrays...
$arraytest =array(5) { =string(2) "39" =string(2) "44" =>
string(2) "77" =string(3) "150" =string(3) "464" }
why do i get NULL value if i try to sort this array using sort command?
sort($arraytest);
|
by: Tartifola |
last post by:
Hi,
I'm working with numerical array and I'm a little lost on a particular
sorting of one of them. In particular I have an array like
a = array(,])
and I need to sort it using only the first column as reference but
keeping the lines together so to obtain
array(,
| |
by: spl |
last post by:
I have an array of objects. I want to sort the array of a
given numeric value that exists in each of the objects. Suppose the
array of objects is called student and the numeric value I want to
sort the array based on the member is called "rollno".
ex:
struct student{
int rollno;
int name;
int status;
};
|
by: dillipkumar |
last post by:
Hi,
I have a Hash:
%hash= 'student' => ,
I want to sort the hash according to sorting of 'roll_no' ..
After sorting It should be displayed
%hash= 'student' => ,
|
by: mohammedrh |
last post by:
Can any body help me on sorting arrays in C#
|
by: dondigital |
last post by:
I'm trying to figure out the best approach to grab data from an array. So heres the deal. I have byte array called curveData filled will hex bytes that I've downloaded from a unit under test. Basically, I am trying to loop through this array of 256 bytes. I want to grab the 1st byte (of a 4 byte chunk), so , then and so on…so every 4th byte...
|
by: tc18a |
last post by:
I'm looking for a fast algorithm for sorting an array of integers with respect to a master list.
For example:
Master List: 7 3 9 1 32 6 5 4 99 100 201 13
Array that needs to be sorted array1: 1 5 4 3 6
The "sorted" array would be: 3 1 6 5 4
The order of the array that needs to sorted is originally random. Neither the master list nor...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |