Re: [PHP] OT - SQL string to get value by latest date only in a join
Add ORDER BY date_col DESC LIMIT 1 Merritt, Dave wrote: All, I apologize up front for being off topic, but I don't want to have to subscribe to other lists unless necessary. I know that someone on this list should be able to help me out. I have the SQL string below that I am running. The problem I'm having is that the history table being called in the join has multiple entries referencing back to the single entry in the customer table. What I want to be able to do is return only the latest/newest entry by date in the history table and not older entries. I assume that I need to add some conditional to the outer join. The problem I have is that I don't know what the conditional would be to allow returning the latest row by date. Thanks in advance, and again I apologize for the OT question, Dave Merritt [EMAIL PROTECTED] currently running this string: SELECT customer.customer_id, customer.customer_name, business_unit.business_unit, customer.created_date, CONCAT_WS(, , username.last_name, username.first_name), history.modify_date, CONCAT_WS(, , modified.last_name, modified.first_name), history.description FROM `customer` LEFT OUTER JOIN `business_unit` ON business_unit.bu_id = customer.bu_id LEFT OUTER JOIN `username` ON username.user_id = customer.creator_id LEFT OUTER JOIN `history` ON ( history.item_id = customer.customer_id AND history.module_id = 1003 ) LEFT OUTER JOIN `username` AS modified ON modified.user_id = history.modifier_id WHERE customer.customer_id LIKE %man% OR customer.customer_name LIKE %man% OR customer.created_date LIKE %man% OR business_unit.business_unit LIKE %man% OR username.first_name LIKE %man% OR username.last_name LIKE %man% OR history.modify_date LIKE %man% OR history.description LIKE %man% OR modified.first_name LIKE %man% OR modified.last_name LIKE %man% ORDER BY customer.customer_name which returns the following results: '1040','Allmand Bros. Inc.','Gas Springs, Industrial','2002-03-01 00:00:00','Admin, PDMWeb','2002-11-12 10:53:23','Burnstingle, Robert','Additional changes made','1003', '1040','Allmand Bros. Inc.','Gas Springs, Industrial','2002-03-01 00:00:00','Admin, PDMWeb','2002-11-09 13:15:34','Merritt, Dave','A test of history','1003', '1050','Ameritool Manufacturing','Gas Springs, Industrial','2002-03-01 00:00:00','Admin, PDMWeb','NULL','','NULL','NULL', '1053','Ammann-Yanmar','Gas Springs, Industrial','2002-03-01 00:00:00','Admin, PDMWeb','NULL','','NULL','NULL', '1065','Art's Way Manufacturing','Gas Springs, Industrial','2002-03-01 00:00:00','Admin, PDMWeb','NULL','','NULL','NULL', . What I'm trying to achieve is the above results both with only one row returned for the 1040 item like so, and the row returned should be the row with the latest/newest modified date: 1040','Allmand Bros. Inc.','Gas Springs, Industrial','2002-03-01 00:00:00','Admin, PDMWeb','2002-11-12 10:53:23','Burnstingle, Robert','Additional changes made','1003', '1050','Ameritool Manufacturing','Gas Springs, Industrial','2002-03-01 00:00:00','Admin, PDMWeb','NULL','','NULL','NULL', '1053','Ammann-Yanmar','Gas Springs, Industrial','2002-03-01 00:00:00','Admin, PDMWeb','NULL','','NULL','NULL', '1065','Art's Way Manufacturing','Gas Springs, Industrial','2002-03-01 00:00:00','Admin, PDMWeb','NULL','','NULL','NULL', . ** Any views, opinions or authorizations contained in this email are solely those of the author and do not necessarily represent those of ArvinMeritor, Inc. If you are not familiar with the corporate authority of the author, please obtain confirmation in writing of the content of this email prior to taking any action on the basis of the information. If you are not the intended recipient, you are hereby notified that any disclosure, copying or distribution of the information enclosed is strictly prohibited. ** -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] OT - SQL string to get value by latest date only in a join
All, I apologize up front for being off topic, but I don't want to have to subscribe to other lists unless necessary. I know that someone on this list should be able to help me out. I have the SQL string below that I am running. The problem I'm having is that the history table being called in the join has multiple entries referencing back to the single entry in the customer table. What I want to be able to do is return only the latest/newest entry by date in the history table and not older entries. I assume that I need to add some conditional to the outer join. The problem I have is that I don't know what the conditional would be to allow returning the latest row by date. Thanks in advance, and again I apologize for the OT question, Dave Merritt [EMAIL PROTECTED] currently running this string: SELECT customer.customer_id, customer.customer_name, business_unit.business_unit, customer.created_date, CONCAT_WS(, , username.last_name, username.first_name), history.modify_date, CONCAT_WS(, , modified.last_name, modified.first_name), history.description FROM `customer` LEFT OUTER JOIN `business_unit` ON business_unit.bu_id = customer.bu_id LEFT OUTER JOIN `username` ON username.user_id = customer.creator_id LEFT OUTER JOIN `history` ON ( history.item_id = customer.customer_id AND history.module_id = 1003 ) LEFT OUTER JOIN `username` AS modified ON modified.user_id = history.modifier_id WHERE customer.customer_id LIKE %man% OR customer.customer_name LIKE %man% OR customer.created_date LIKE %man% OR business_unit.business_unit LIKE %man% OR username.first_name LIKE %man% OR username.last_name LIKE %man% OR history.modify_date LIKE %man% OR history.description LIKE %man% OR modified.first_name LIKE %man% OR modified.last_name LIKE %man% ORDER BY customer.customer_name which returns the following results: '1040','Allmand Bros. Inc.','Gas Springs, Industrial','2002-03-01 00:00:00','Admin, PDMWeb','2002-11-12 10:53:23','Burnstingle, Robert','Additional changes made','1003', '1040','Allmand Bros. Inc.','Gas Springs, Industrial','2002-03-01 00:00:00','Admin, PDMWeb','2002-11-09 13:15:34','Merritt, Dave','A test of history','1003', '1050','Ameritool Manufacturing','Gas Springs, Industrial','2002-03-01 00:00:00','Admin, PDMWeb','NULL','','NULL','NULL', '1053','Ammann-Yanmar','Gas Springs, Industrial','2002-03-01 00:00:00','Admin, PDMWeb','NULL','','NULL','NULL', '1065','Art's Way Manufacturing','Gas Springs, Industrial','2002-03-01 00:00:00','Admin, PDMWeb','NULL','','NULL','NULL', . What I'm trying to achieve is the above results both with only one row returned for the 1040 item like so, and the row returned should be the row with the latest/newest modified date: 1040','Allmand Bros. Inc.','Gas Springs, Industrial','2002-03-01 00:00:00','Admin, PDMWeb','2002-11-12 10:53:23','Burnstingle, Robert','Additional changes made','1003', '1050','Ameritool Manufacturing','Gas Springs, Industrial','2002-03-01 00:00:00','Admin, PDMWeb','NULL','','NULL','NULL', '1053','Ammann-Yanmar','Gas Springs, Industrial','2002-03-01 00:00:00','Admin, PDMWeb','NULL','','NULL','NULL', '1065','Art's Way Manufacturing','Gas Springs, Industrial','2002-03-01 00:00:00','Admin, PDMWeb','NULL','','NULL','NULL', . ** Any views, opinions or authorizations contained in this email are solely those of the author and do not necessarily represent those of ArvinMeritor, Inc. If you are not familiar with the corporate authority of the author, please obtain confirmation in writing of the content of this email prior to taking any action on the basis of the information. If you are not the intended recipient, you are hereby notified that any disclosure, copying or distribution of the information enclosed is strictly prohibited. ** -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] OT - SQL string to get value by latest date only in a join
[snip] SELECT customer.customer_id, customer.customer_name, business_unit.business_unit, customer.created_date, CONCAT_WS(, , username.last_name, username.first_name), history.modify_date, CONCAT_WS(, , modified.last_name, modified.first_name), history.description FROM `customer` LEFT OUTER JOIN `business_unit` ON business_unit.bu_id = customer.bu_id LEFT OUTER JOIN `username` ON username.user_id = customer.creator_id LEFT OUTER JOIN `history` ON ( history.item_id = customer.customer_id AND history.module_id = 1003 ) LEFT OUTER JOIN `username` AS modified ON modified.user_id = history.modifier_id WHERE customer.customer_id LIKE %man% OR customer.customer_name LIKE %man% OR customer.created_date LIKE %man% OR business_unit.business_unit LIKE %man% OR username.first_name LIKE %man% OR username.last_name LIKE %man% OR history.modify_date LIKE %man% OR history.description LIKE %man% OR modified.first_name LIKE %man% OR modified.last_name LIKE %man% ORDER BY customer.customer_name [/snip] Try this; SELECT customer.customer_id, customer.customer_name, business_unit.business_unit, customer.created_date, CONCAT_WS(, , username.last_name, username.first_name), MAX(history.modify_date), CONCAT_WS(, , modified.last_name, modified.first_name), history.description FROM `customer` LEFT OUTER JOIN `business_unit` ON business_unit.bu_id = customer.bu_id LEFT OUTER JOIN `username` ON username.user_id = customer.creator_id LEFT OUTER JOIN `history` ON ( history.item_id = customer.customer_id AND history.module_id = 1003 ) LEFT OUTER JOIN `username` AS modified ON modified.user_id = history.modifier_id WHERE customer.customer_id LIKE %man% OR customer.customer_name LIKE %man% OR customer.created_date LIKE %man% OR business_unit.business_unit LIKE %man% OR username.first_name LIKE %man% OR username.last_name LIKE %man% OR history.modify_date LIKE %man% OR history.description LIKE %man% OR modified.first_name LIKE %man% OR modified.last_name LIKE %man% GROUP BY customer.customer_name ORDER BY customer.customer_name Note the MAX(history.modify_date) in the SELECT and the GROUP BY customer_name HTH! Peace ... Jay -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
