custom sql statment required

**devsusen** · Feb 25 '10, 07:16 AM

Hi,

I require help to design a sql statement to get some specific dataset. This sql statement I need to run using php script.
Let me first describe the table structure. Table 'tblservicedeta ils' contains the following fields:

Code:

'srv_id' INT(11)  NOT NULL AUTO_INCREMENT PRIMARY KEY ,
'srv_mcid' INT(6) NOT NULL ,
'srv_jobcodeid' INT(2) NOT NULL ,
'srv_date' INT(11) NOT NULL DEFAULT 0 
......

Now when I run the following sql statement :

Code:

SELECT srv_id, srv_mcid, srv_jobcodeid, srv_date
FROM tblservicedetails
WHERE srv_mcid IN (264, 460, 870) AND srv_jobcodeid IN (13, 14)

I get the following result:

Code:

srv_id | srv_mcid | srv_jobcodeid | srv_bkdate
11     | 870        | 13                 | 1254790800
16     | 870        | 14                 | 1264361400
178    | 264        | 14                 | 1265997600
242    | 460        | 14                 | 1272153600
245    | 264        | 13                 | 1267299000

Now The result set I wish to populate is

Code:

srv_id | srv_mcid | srv_jobcodeid | srv_bkdate
16     | 870        | 14                 | 1264361400
242    | 460        | 14                 | 1272153600
245    | 264        | 13                 | 1267299000

This result set are the highest srv_bkdate for each srv_mcid in the given list
with srv_jobcodeid as 13 or 14.

What should be the sql statement that can provide this above result set?

In where condition the list of values for srv_mcid will vary and actually it will be populated dynamically in php.

I need your help to design the sql statement. I am not very sure if I need to write a subquery for this.