I am trying to find the last occurance of a entry rather then the first.

Table:

ID REV
1 A
1 B
2 X
2 Y

What I currently have:

SELECT *
FROM tbl_sod_status
GROUP BY `sod_id`
ORDER BY rev DESC

Resulting query:

ID REV
1 A
2 X

What I am trying to get is:

ID REV
1 B
2 Y

Still working it but have found a site that gives some ideas on how to do it but still struggling. If someone feels up for the challenge have a go and see if they can figure it out.

http://www.mvps.org/access/queries/qry0020.htm

Copy of table:

-- phpMyAdmin SQL Dump
-- version 3.3.5
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Oct 27, 2010 at 11:00 PM
-- Server version: 5.1.49
-- PHP Version: 5.3.3
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
--
-- Database: `petdb`
--
-- --------------------------------------------------------
--
-- Table structure for table `tbl_sod_status`
--
CREATE TABLE IF NOT EXISTS `tbl_sod_status` (
`sod_status_id` int(11) NOT NULL AUTO_INCREMENT,
`sod_id` int(11) NOT NULL,
`date_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`rev` varchar(3) DEFAULT NULL,
`pages` int(11) DEFAULT NULL,
`status` varchar(45) DEFAULT NULL,
`eng_error` int(11) DEFAULT NULL,
`draft_error` int(11) DEFAULT NULL,
`vendor_error` int(11) DEFAULT NULL,
`sourcing_error` int(11) DEFAULT NULL,
`value_eng` int(11) DEFAULT NULL,
`requested_change` int(11) DEFAULT NULL,
`spec_conflict` int(11) DEFAULT NULL,
`direction_deficient` int(11) DEFAULT NULL,
`schedule_issue` int(11) DEFAULT NULL,
`mfg_error` int(11) DEFAULT NULL,
`rtpp` tinyint(4) NOT NULL,
`comments` varchar(255) NOT NULL,
PRIMARY KEY (`sod_status_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;
--
-- Dumping data for table `tbl_sod_status`
--
INSERT INTO `tbl_sod_status` (`sod_status_id`, `sod_id`, `date_stamp`, `rev`, `pages`, `status`, `eng_error`, `draft_error`, `vendor_error`, `sourcing_error`, `value_eng`, `requested_change`, `spec_conflict`, `direction_deficient`, `schedule_issue`, `mfg_error`, `rtpp`, `comments`) VALUES
(7, 1871, '2010-10-26 12:10:12', 'A', 1, '', 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 0, 'HELLO'),
(8, 1871, '2010-10-26 13:47:03', 'B', 1, NULL, 11, 21, 31, 41, 51, 61, 71, 81, 91, 101, 0, 'HELLO'),
(15, 1872, '2010-10-28 12:16:31', 'x', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, ''),
(14, 1872, '2010-10-27 12:16:19', 'y', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, '');

Use a UNION query

I don't understand how you figure that would work. I am looking to create a DISTINCT list capturing the latest version of the document however DISTINCT compares all values in a record set so some how I am trying to make a query statement that will return a DISTINCT record for each ID and the latest REV.

Here is the winning statement:

select t1.sod_id, t1.rev, t1.eng_error
from tbl_sod_status t1
inner join
(select sod_id, max(rev) as max_rev from tbl_sod_status group by sod_id) t2
on t1.sod_id = t2.sod_id
and t1.rev = t2.max_rev;

select [ID],MAX(REV) [Recent]


from
(
select 1 [ID], 'A' [REV] union ALL
select 1, 'B' union ALL
select 2, 'X' union ALL
select 2, 'Y'
) TmpTable

group By [ID]