Need help with a complicated SELECT query
TableName
Field( Type, Description )
options
job_code( varchar(9), Alphanumeric, i.e: TY273, 1500 )
trade_code( varchar(6), Alphanumeric, i.e: TRE42, GHY04 )
opt_num( char( 3 ), Numeric string, i.e: 000, 001, 110, 220 )
jobs
job_code( Same as 'job_code' in table 'options' )
proj_code( varchar(6), Alphanumeric, i.e: 15000, 831, CD47 )
Variables:
o = options table
j = jobs table
$PROJECT = php variable corresponding to j.proj_code
$TRADE = php variable corresponding to o.trade_code
I'm using the following query:
SELECT o.job_code, o.trade_code, o.opt_num FROM options o, jobs j WHERE j.job_code = o.job_code AND o.trade_code = $TRADE AND j.proj_code = $PROJECT
Resulting in something like the following:
-> (1500, 2B2W, 000)
-> (1501, 2B2W, 000)
-> (1500, 2B2W, 001)
-> (1500, 2B3W, 001)
-> (1501, 2B3W, 001)
-> (1502, 2B3W, 001)
Given two job_codes, J1 and J2, J2 > J1 implies J2 is more recent. I need to modify the SQL query to select only the most recent job_codes for each unique combination of the trade_code and opt_num.
For example, given that the combination 2B2W000 exists in multiple job_codes (1500 and 1501), I need to select the row with the most recent job_code.
It's not enough choose the rows with the MAX(job_code) because there can exist combinations of the trade_code and opt_num fields that do not have job_code = MAX(job_code).
For example, if 2B2W000 exists in job_codes 1500 and 1501 and 2B2W001 exists only in job_code 1500, I need the query to return
-> (1501, 2B2W, 000)
-> (1500, 2B2W, 001)
So, given my original query results:
-> (1500, 2B2W, 000)
-> (1501, 2B2W, 000)
-> (1500, 2B2W, 001)
-> (1500, 2B3W, 001)
-> (1501, 2B3W, 001)
-> (1502, 2B3W, 001)
I need to filter to the following:
-> (1501, 2B2W, 000)
-> (1500, 2B2W, 001)
-> (1502, 2B3W, 001)
I greatly appreciate any help in regards to this problem.