Hi,

Can anybody help, I have the following query below.

SELECT jobid, cssjob, busresid, ccd, rag, pt1b, pt1c, rt1b, rt1c, received, waiterstatus, cssdataid
FROM dbo.cssdata
WHERE (waiterstatus = 0)

Whic produces the following results, (this is a snapshot)

jobid Cssjob busres ccd rag pt1b pt1c rt1c cssdataid

118300 MC R 01/03/2005 1 0 1 11456
118300 AX R 01/03/2005 1 1 1 11452
118300 CE B 01/03/2005 2 1 1 11451
118900 FR A 01/03/2005 3 1 1 11500
118900 CD A 01/03/2005 2 1 1 11499
118900 MS A 01/03/2005 1 1 1 11478
118900 LM A 01/03/2005 1 1 1 11473

I need help in the following, I need for each jobid, to select the rcord with the hihest cssdataid, so for 118300, I need to select the record which contains cssdataid = 11456, I will always need to select the record with latest (highest) cssdataid number. Also I want to be able to to sum up pt1b, pt1c, rt1c and group by jobid. The result I am looking for is below

jobid Cssjob busres ccd rag pt1b pt1c rt1c cssdataid

118300 MC R 01/03/2005 4 2 3 11456
118900 FR A 01/03/2005 7 4 4 11500

How can I do this

Thanks

Mark

First of all normalize you table design

Let's try this

SELECT JobId, Cssjob, BusRes, ccd, rag, Sumpt1b, Sumpt1c, Sumrt1c, MaxCssDataId
FROM dbo.cssdata Main,
    (SELECT JobId MaxJobId,MAX(CssDataId) MaxCssDataId
     FROM dbo.cssdata
     WHERE waiterstatus = 0
     GROUP BY JobId) AS MaxCDI,
    (SELECT JobId SumJobId,SUM(pt1b) Sumpt1b,SUM(pt1c) Sumpt1c,SUM(pt1c) Sumrt1c,
     FROM dbo.cssdata
     WHERE waiterstatus = 0
     GROUP BY JobId) AS SumCDI
WHERE WaiterStatus = 0
AND Main.JobId=MaxCDI.MaxJobId AND Main.CssDataId=MaxCDI.MaxCssDataId
AND Main.JobId=SumCDI.SumJobId

I have used two sub queries MaxCDI and SumCDI.

Cheers,
Pooja Falor