SQL Help !!!!!

[Viji]

Hi,
I am trying to get the latest report date and the relevant well no from a group of wells.I have the following SQL.

SELECT Max(DTE_UPDATED) , WELL FROM TEST_DATA
WHERE (((WELL)=1 Or (WELL)=2 Or (WELL)=3 Or (WELL)=4));


I get an erro message which says
"You tried to execute a query that doesn't include the specified expression as part of an aggregate function or grouping."

If I just get the report date (i.e. w/o the well) it works fine. But I want the latest report date for this group of wells as well as the relavent well no for the latest report (i.e. it culd be 1,2,3 or 4)

Can somone point out the error in my SQL

Cheers

Vijja

[Iouri]

I believe that you cannot use MAX function with Fields (like WELL) together

Iouri Boutchkine
iouri@hotsheet.com

[TH1]

Change your SQL to
SELECT Max(DTE_UPDATED) , WELL FROM TEST_DATA
WHERE (((WELL)=1 Or (WELL)=2 Or (WELL)=3 Or (WELL)=4)) GROUP BY WELL;

[Clearcode]

SELECT Well, Max(DTE_UPDATED)
From TEST_DATE
Where Well IN (1,2,3,4)
Group By Well


That should do the trick.

HTH,
D.

-------------------------------------------------
Ex. Datis: Duncan Jones
Merrion Computing Ltd
http://www.merrioncomputing.com