beyzad Posted October 27, 2012 Share Posted October 27, 2012 Hi there. I have a problem again I have 7 fields named `factor_1_time`,`factor_2_time`,`factor_3_time`,`factor_4_time`,`factor_5_time`,`factor_6_time`,`factor_7_time` and each of them will hold a timestamp. I will never know which one of them have the maximum value. In other word, I dont know which onw of the following fields was updated last. Is there any query to get the field name that holds maximum value? Thanks Quote Link to comment Share on other sites More sharing options...
Jessica Posted October 27, 2012 Share Posted October 27, 2012 (edited) You need to normalize your data. Each of those should be it's own row. Google "normalization". Edited October 27, 2012 by Jessica Quote Link to comment Share on other sites More sharing options...
Barand Posted October 27, 2012 Share Posted October 27, 2012 (edited) Hi there. I have a problem again By designing tables like that, all problems are of your own creation. Try Googling "Data normalization". EDIT: Previous post warnings need a great deal of improvement with this software. Edited October 27, 2012 by Barand Quote Link to comment Share on other sites More sharing options...
beyzad Posted October 27, 2012 Author Share Posted October 27, 2012 Hi again. Well i think i have to combine it with PHP. So i will export value of all the fields, the use max(). Thanks btw. Quote Link to comment Share on other sites More sharing options...
Jessica Posted October 27, 2012 Share Posted October 27, 2012 So, this morning my fridge started to flood. My husband, being a smart man, listened to me when I said it was happening when we tried to use the water dispenser, and together we figured out that the water filter had frozen and cracked. He then used the collective knowledge of the people around him to decide that 36 degrees was too cold for our fridge, and we should replace the filter. If I was married to this guy, we'd simply move the fridge into the tub so the water would drain out there, and eat frozen yogurt. By which I mean the yogurt that had frozen within the fridge, not the delicious dessert. When we'd want to take a bath, we'd probably go outside and use the kiddie pool for that. And so on, and so on. Now I want some fro-yo. Quote Link to comment Share on other sites More sharing options...
beyzad Posted October 27, 2012 Author Share Posted October 27, 2012 Hi. That was a nice story Sorry if i refused to use the way you guys said. My database structure is some kinda different with what you think. Regards. Quote Link to comment Share on other sites More sharing options...
Jessica Posted October 27, 2012 Share Posted October 27, 2012 No, we know exactly how your database is structured. And it's wrong. Quote Link to comment Share on other sites More sharing options...
beyzad Posted October 27, 2012 Author Share Posted October 27, 2012 (edited) Hi. Ok i think it's still not too late to change my structure then. I'm not sure if i should post it here or not, so here we go: I have a table that contains many informations about incoming orders. The table name is factor. Each order may have any status from 1 to 7. But can only have one of them. Also each order has a unique key stored in 'factor_key' field. There is a long long (i mean time) process that will specify the order status. but no one will now which status will be the end of the order's journey. so i made 7 fields for storing 7 varriable time. Everytime my order's status changed, the time will stored in the respective field. There will be high amount of data in this table. Please tell me how should i change this. Regards. Edited October 27, 2012 by beyzad Quote Link to comment Share on other sites More sharing options...
Jessica Posted October 27, 2012 Share Posted October 27, 2012 Google "normalization". Try Googling "Data normalization". Quote Link to comment Share on other sites More sharing options...
Barand Posted October 27, 2012 Share Posted October 27, 2012 This query will return a value between 1 and 7 depanding on which has the latest date. However you don't want to be writing queries like this all the time. Corrrectly normalized, the queries will be far more straightforward. SELECT FIND_IN_SET( GREATEST(factor_1_time,factor_2_time,factor_3_time,factor_4_time, factor_5_time,factor_6_time,factor_7_time), CONCAT_WS(',',factor_1_time,factor_2_time,factor_3_time,factor_4_time, factor_5_time,factor_6_time,factor_7_time) ) as latest FROM factors Quote Link to comment Share on other sites More sharing options...
Barand Posted October 28, 2012 Share Posted October 28, 2012 (edited) Here's what you need to do. From this | orderid | factor_1_time | factor_2_time | ... | factor_6_time | factor_7_time | +---------+---------------------+---------------------+-----+---------------------+---------------------+ | 1 | 2012-10-28 06:45:54 | 2012-10-28 11:57:26 | ... | 2012-10-28 17:31:28 | 2012-10-28 12:54:05 | | 2 | 2012-10-28 09:35:37 | 2012-10-28 08:24:39 | ... | 2012-10-28 16:15:00 | 2012-10-28 08:09:19 | transform to +---------+---------------------+--------+ | orderid | factor_time | status | +---------+---------------------+--------+ | 1 | 2012-10-28 06:45:54 | 1 | | 1 | 2012-10-28 11:57:26 | 2 | | 1 | 2012-10-28 06:32:38 | 3 | | 1 | 2012-10-28 03:51:40 | 4 | | 1 | 2012-10-28 21:58:39 | 5 | | 1 | 2012-10-28 17:31:28 | 6 | | 1 | 2012-10-28 12:54:05 | 7 | | 2 | 2012-10-28 09:35:37 | 1 | | 2 | 2012-10-28 08:24:39 | 2 | | 2 | 2012-10-28 05:11:15 | 3 | | 2 | 2012-10-28 00:26:40 | 4 | | 2 | 2012-10-28 14:06:20 | 5 | | 2 | 2012-10-28 16:15:00 | 6 | | 2 | 2012-10-28 08:09:19 | 7 | +---------+---------------------+--------+ Edited October 28, 2012 by Barand Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.