MySQL按部件号排序日期

Question

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.

Answer 1

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