Essentially I want these parts (below) grouped then the groups place in order of time, starting from the latest time being at the top of the list.
ID Parts Time 1 SMH_2010 08:59:18 2 JJK_0101 08:59:26 3 FTD_0002 08:59:24 4 JJK_0102 08:59:27 5 FTD_0001 08:59:22 6 SMH_2010 08:59:20 7 FTD_0003 08:59:25
So, the results would look like:
ID Parts Time 1 JJK_0101 08:59:26 2 JJK_0102 08:59:27 3 FTD_0001 08:59:22 4 FTD_0002 08:59:24 5 FTD_0003 08:59:25 6 SMH_2010 08:59:20 7 SMH_2010 08:59:18
Please, I would be grateful for any help.
I finally found a solution to this. Not my ideal solution but, never the less it works.
I added another field called max_date which by default is ‘now()’ as every new part is inserted.
I create a prefix from the current part being inserted, something like “SMH_” as a variable called $prefix = “SMH_”;
I have another query that directly follows the insert, which updates the max_date again, by ‘now()’ where the prefix is like $prefix.
UPDATE parts SET max_date = now() WHERE prefix LIKE '%$prefix%'
To display the results I use something along the line of :
SELECT * FROM parts ORDER BY parts.max_date DESC, parts.part ASC
What you are asking is not sorting in the traditional meaning. Your first attempt orders the result by time, and then by part if multiple timestamps occur at the same time.
What you want neither sorts the result in alphabetically by Parts name, nor ascending/descending on timestamp. What you are asking for can't be accomplished by the sort operation in SQL. Having the parts in sequence is not ordering.