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
