[RESOLVED] Ordering results problem

[UKlee]

Hi Guys hope you had great holidays, I have a script that looks at all the flights in a table based upon a members number then adds all of their time up sounds simple and its working great, however when I go to order my results by total wich is really the time I get well a pretty random result. Confused? I am 🙂 Instead of listing the highest hours at the top it simply looks at the first digit ie. 1 as aposed to 1001 and im not sure how to fix this.

Here is an example of the query

SELECT Distinct(pilot_id) as person, sec_to_time(sum(time_to_sec(time))) as total FROM allflights  group by person order by total  DESC;  

I have also made a test page for you to view I have hilighted the person with the most hours in green for quickness. and echo the query for your reference on the page also.

I have looked around for a solution but have been unable to locate anything of use.

Thanks for taking the time to look at this

Kind regards

Lee

[cahva]

Im quite tired right now but I assume that mysql orders those by natural order, not numerical. Create one more field to that:

SELECT Distinct(pilot_id) as person,
SEC_TO_TIME(SUM(TIME_TO_SEC(time))) total,
SUM(TIME_TO_SEC(time)) sum2 
FROM allflights 
GROUP by id 
ORDER BY sum2 DESC

There must be more clever way but like I said Im tired now 🙂

[UKlee]

Thanks for Taking the time to reply cahya, That solution did work and did order the hours accordingly ,time etc seems to be a pain numericaly thanks for your help and hope you got some rest 🙂

kind regards
Lee