Page 1 of 1

ORDER BY

Posted: Tue Apr 18, 2006 7:25 am
by stebut05
Hi all,

Thanks for taking a look 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, whereas i need to display letters then number 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 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,


Steven

Posted: Tue Apr 18, 2006 7:52 am
by feyd
You'll need to create logic for sorting like that using IF() and other functionality to help or you'll have to do that in PHP.

Posted: Tue Apr 18, 2006 9:14 am
by stebut05
Hi, this has changed a little now.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 etc, etc, etc,

Any ides on the logic, ordering, etc

Thanks, ste

Posted: Tue Apr 18, 2006 11:19 am
by raghavan20
can you show the show create table output for this table and display the query as well.

Posted: Tue Apr 18, 2006 11:33 am
by RobertGonzalez
stebut05 wrote:Hi, this has changed a little now.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,
What logic is this? This looks like you want to see 'PRE' prefixed items, in order by their numerical suffix, then all numerical items, then all 'POS' prefixed items in order by their numerical suffix? Is that the correct order? I am not sure any database would order these records in that order without some serious user defined functions.

Posted: Tue Apr 18, 2006 11:47 am
by Gambler
What database is this?