[RESOLVED] Order By

[stebut05]

Hi all,

Thaks for looking at this thread. I have a database query that retuens the results correctly, but i need to do an order by clause. When i do this it displays numbers before letters,

Jobcard No Page No.
455101 page 1
455101 page 2
455101 page 3
567070 page 1
PRE001 page 1
PRE002 page 1
POS001 page 1
POS001 page 2
POS002 page 1 etc, etc, etc,


whereas i need to display letters, number then letter again e.g

Jobcard No Page No.
PRE001 page 1
PRE002 page 1
POS001 page 1
POS001 page 2
POS002 page 1
455101 page 1
455101 page 2
455101 page 3
567070 page 1
POS001 page 1
POS001 page 2
POS002 page 1 etc, etc, etc,

the field Jobcard No. is a Varchar i think i need to changed to asci and Order By that way. I'm not sure though, any ideas??? Thanks for any help, advice, thoughts in advance.

Kind Regards,

[chazzy]

but how exactly does that order make sense?

[stebut05]

Sorry, should be:

Jobcard No Page No.
PRE001 page 1
PRE002 page 1

455101 page 1
455101 page 2
455101 page 3
567070 page 1

POS001 page 1
POS001 page 2
POS002 page 1 etc, etc, etc,

Not:

Jobcard No Page No.
PRE001 page 1
PRE002 page 1
POS001 page 1
POS001 page 2
POS002 page 1
455101 page 1
455101 page 2
455101 page 3
567070 page 1
POS001 page 1
POS001 page 2
POS002 page 1 etc, etc, etc,


these are jobcards that start at pre, then regular cards (all numeric), then post checks.

Regards,

Steven

[aaronbdavis]

...
these are jobcards that start at pre, then regular cards (all numeric), then post checks.
...
This is the clue right here: simply add a new column called type (or something like that) and set it to datatype number.
0 for Pre
1 for Regular
2 for Post

Then you can sort on that field.
If you think you may add more types later, you can set up a foreign key constraint to a table with (e.g.) the below structure.
CREATE TABLE Jobcard_Types (
idtype number,
description varchar2(20),
PRIMARY KEY (idtype)
)

[stebut05]

Hi aaronbdavis,

Great minds think alike!! I have just seen your reply and much appreciated. I am already in progress of implementing what you have suggested. Thanks everyone for your help, advice, suggestions, etc

Regards,

Steven