Query for Distinct Parameter with newest Date

I am having trouble setting up a query for my inspection test results for a given work piece.

Example Table [Inspection Data]

JOBSERIALPARAMMINMAXVALUEPFDATETIME

11Test101011F6/3/2007

11Test10105P6/4/2007

11Test2286P6/3/2007

11Test2281F6/4/2007

12Test10104P6/3/2007

12Test2285P6/4/2007

11Test3687P6/3/2007

Query table [Inspection Data] for:

JOB = 1

SERIAL = 1

MAX( DATETIME ) for each test

Expected Results:

JOBSERIALPARAMMINMAXVALUEPFDATETIME

11Test10105P6/4/2007

11Test2281F2/4/2007

11Test3687P6/3/2007

Thanks,

Sam

Here you go...

Code Snippet

Create Table #inspectiondata (

[JOB] int ,

[SERIAL] int ,

[PARAM] Varchar(100) ,

[MIN] int ,

[MAX] int ,

[VALUE] int ,

[PF] Varchar(100) ,

[DATETIME] Datetime

);

Insert Into #inspectiondata Values('1','1','Test1','0','10','11','F','6/3/2007');

Insert Into #inspectiondata Values('1','1','Test1','0','10','5','P','6/4/2007');

Insert Into #inspectiondata Values('1','1','Test2','2','8','6','P','6/3/2007');

Insert Into #inspectiondata Values('1','1','Test2','2','8','1','F','6/4/2007');

Insert Into #inspectiondata Values('1','2','Test1','0','10','4','P','6/3/2007');

Insert Into #inspectiondata Values('1','2','Test2','2','8','5','P','6/4/2007');

Insert Into #inspectiondata Values('1','1','Test3','6','8','7','P','6/3/2007');

--For SQL Server 2005

;With CTE

as

(

Select *, Row_Number() OVER(Partition By PARAM Order By [DATETIME] Desc) RowId From #inspectiondata

Where [JOB] = 1 And [SERIAL] = 1

)

Select

[JOB]

,[SERIAL]

,[PARAM]

,[MIN]

,[MAX]

,[VALUE]

,[PF]

,[DATETIME]

From

CTE

Where

RowId = 1

--For SQL Server 2000

Select

Data.[JOB]

,Data.[SERIAL]

,Data.[PARAM]

,Data.[MIN]

,Data.[MAX]

,Data.[VALUE]

,Data.[PF]

,Data.[DATETIME]

From

#inspectiondata Data

Join (

Select

Max([DateTime]) [DateTime]

,PARAM

From

#inspectiondata

Where

[JOB] = 1 And [SERIAL] = 1

Group By

PARAM

) as MaxData On MaxData.[DateTime] = Data.[DateTime] And MaxData.PARAM = Data.PARAM

Where

[JOB] = 1

And [SERIAL] = 1

|||

I was not familiar with CTEs until I read your posting and they seem very easy to read but I cannot get it to compile. The error I get if I name the CTE 'CTE' is 'CTE is not a recognized option'.

Are CTEs available with the Express version of SQL Server?

|||first google result would support this

http://msdn2.microsoft.com/en-us/library/bb264566(SQL.90).aspx

Give the error of why it won't compile.|||

The reason why it was not working is because I had an alter procedure call at the beginning of the stored procedure.

Everything is working perfectly now.

Thank you for your help.

|||

Also, The Alter Procedure call had to be moved to the first line in the sql code.

Thanks again,

Sam