mongoose00318 Posted July 8, 2021 Share Posted July 8, 2021 I'm trying to figure out how to select job numbers and their line items by searching for a job number but only their latest entry in the table. This table is an archive table. As changes are made to rows in the main production_data table, the row's current values are backed up in this archive table. Eventually an order falls off completely and only exists in this archive table. Here is the table schema -- -- Table structure for table `production_data_archive` -- DROP TABLE IF EXISTS `production_data_archive`; CREATE TABLE IF NOT EXISTS `production_data_archive` ( `id` int(11) NOT NULL AUTO_INCREMENT, `order_id` int(11) NOT NULL, `job_number` int(8) NOT NULL, `enterprise` tinytext NOT NULL, `part_number` text NOT NULL, `description` text NOT NULL, `psm` tinytext NOT NULL, `qty` int(11) NOT NULL, `line_item` varchar(11) NOT NULL, `as400_ship_date` date DEFAULT NULL, `hold_reason` text NOT NULL DEFAULT '0', `hold_date` date DEFAULT NULL, `insert_time` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'time order was inserted', `archive_time` timestamp NULL DEFAULT NULL COMMENT 'this column was added 4-19-21', PRIMARY KEY (`id`), KEY `job_line` (`job_number`,`line_item`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; COMMIT; Here is a sample of data when I do a search for a specific job_number: In the image above I search for a job_number and then highlighted the most recent entries for that job_number. Those are the records I would have wanted to come out of the query. Quote Link to comment Share on other sites More sharing options...
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.