ORDER BY

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
stebut05
Forum Commoner
Posts: 35
Joined: Wed Sep 21, 2005 9:29 am
Location: Liverpool, UK

ORDER BY

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
stebut05
Forum Commoner
Posts: 35
Joined: Wed Sep 21, 2005 9:29 am
Location: Liverpool, UK

Post 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
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

can you show the show create table output for this table and display the query as well.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
Gambler
Forum Contributor
Posts: 246
Joined: Thu Dec 08, 2005 7:10 pm

Post by Gambler »

What database is this?
Post Reply