custom sort order

[MontyBurns]

Hi,

I know it's possible to create a query, and in the sort order clause call a Function or procedure to sort the records in a custom manner, but i'm unsure of the best way to code this proc.

I've got file numbers like:
A1.1
A1.10
A1.11
A1.12
A1.17
A1.2
etc.

Obviously this isn't the correct order for them as A1.2 should come before A1.10.

Would you use arrays, recordsets or what - i'm really not sure where to start!

Any help would be really appreciated,
Burns

 

[CasperTFG]

You could change your numbering scheme to:
A1.001
A1.010
A1.011
A1.012
A1.017
A1.002

Or you could make these seperate columns
Column1 Coulumn2
A1 1
A1 10
A1 11
A1 12
A1 17
A1 2
Craig, mailto:sander@cogeco.ca

"Procrastination is the art of keeping up with yesterday."

I hope my post was helpful!!!

 

[MontyBurns]

thanks Craig, but unfortunately I didn't set this DB up, and the data's already in there.

Craig/Anyone got any other ideas?

Cheers,
Burns

 

[CasperTFG]

Un fortuantly I don't think there is a way to do it. The problem is that once you introduce Characters into a field the sort oder becomes Alpha based and so it ignores the values of the numbering scheme.
Craig, mailto:sander@cogeco.ca

"Procrastination is the art of keeping up with yesterday."

I hope my post was helpful!!!

 

[MontyBurns]

I remember in a previous job taking a module from a DB of unknown author, and using it to reformat addresses so as the Road name came first and then the flat number etc., put this into a new Aliased field and ordered on that.

You simply called it from a query something like:
SELECT *
FROM tblAddresses
ORDER BY ProcName(fieldName) AS OrderedAddress

It was a little too complicated for me to understand at the time, but I reckon I could work it out now if someone had some similar code they could post.

Thanks,
Burns

 

[CasperTFG]

I thin that Procname is a reference to an SQL function. I don't know if anyone out here may be able to give you a function that will parce the field. Craig, mailto:sander@cogeco.ca

"Procrastination is the art of keeping up with yesterday."

I hope my post was helpful!!!