• Status: Solved
• Priority: Medium
• Security: Public
• Views: 507

# Mysql

I have a mysql table called schedules with a field called hour which is a char(2) and one called sequence which is int(8).  I would like to do

SELECT * FROM schedules ORDERED BY hour AND sequence;

When I use the above command the hour sort is not correct because it is sorting as a char field as opposed to integer and the sequence sort is highest number first.  I need What do I need to change or do.  Thanks!
0
sargent240
• 3
• 3
1 Solution

Fixer of ProblemsCommented:
Try this...
``````SELECT * FROM schedules ORDER BY CAST(hour AS UNSIGNED) AND sequence;
``````
0

ArtistCommented:
The most obvious thing to do is change the hour field to int(2).

If the hour field always has a 2-digit value from 00 to 24, then you could sort it in numeric order by using ASC or DESC.

You can ORDER BY hour DESC, sequence ASC  or whatever ordering you want.  Point is that the two fields can be sorted separately.

If the hour field sometimes has a 2-digit number and sometimes a 1-digit number, I fear you have a big problem.  You could maybe add a leading 0 in the latter case.
0

Fixer of ProblemsCommented:
Or try this...
``````SELECT * FROM schedules ORDER BY CAST(hour AS UNSIGNED) ASC, sequence ASC;
``````
0

Author Commented:
David, the command you suggested works fine.  The only problem I have is they are descending and I need both to be ascending.  I tried this but it gave me an error.

SELECT * FROM schedules where scheduleId = '1662' ORDER BY CAST(hour AS UNSIGNED) ASC AND sequence ASC;

If I only do a sort on the hour it works fine and is ascending.  What do ya think.
0

Author Commented:
David.  Your second post did things just as I need them.   THANKS!
0

Author Commented:
Perfect
0

Fixer of ProblemsCommented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.