Selecting and Importing Data Correlating with Most Recent Date with Microsoft Query SQL

Question

I'm trying to modify a current SQL code in Microsoft Query I created to return the most recent production test conducted on a well. Right now the query below returns a list of tests for any well from [Date Parameter=01/01/08] to now for [Well Parameter= Well#1]. Also, to get all the data I need, I must join two tables, one with well names, one with production data, and both contain a unique well # which I join.

SELECT 
P.TEST_DT, P.BOPD, P.BWPD, P.MCFD
FROM 
WELL_TABLE C, PRODTEST_TABLE P
WHERE 
C.UNIQUE_WELL_ID = P.UNIQUE_WELL_ID 
AND ((C.WELL_NAME=?) 
AND (P.TEST_DT>=?))
ORDER BY P.TEST_DT DESC

Right now my table looks as such:

TEST_DT    P.BOPD  P.BWPD  P.MCFD
9/23/2012  23      125    0
8/23/2010  21      137    0
7/15/2009  29      123    0

I would like to just return the most recent test:

TEST_DT    P.BOPD  P.BWPD  P.MCFD
9/23/2012  23      125     0

Any help would be greatly appreciated... I've tried working with max(TEST_DT) but have been unsuccessful.

Where's MSQuery getting data from, MS Access or SQL Server? – RichardTheKiwi Oct 12 '12 at 20:22 — RichardTheKiwi, Oct 12 '12 at 20:22
data is from Microsoft ODBC for Oracle – user1742149 Oct 12 '12 at 20:47 — user1742149, Oct 12 '12 at 20:47

score 0 · Answer 1 · answered Oct 12 '12 at 21:49

0

SELECT tab.* 
  FROM (SELECT P.TEST_DT, P.BOPD, P.BWPD, P.MCFD
          FROM  WELL_TABLE C, PRODTEST_TABLE P
         WHERE C.UNIQUE_WELL_ID = P.UNIQUE_WELL_ID 
           AND C.WELL_NAME=?
         ORDER BY P.TEST_DT DESC) tab
 WHERE ROWNUM=1;

answered Oct 12 '12 at 21:49

Anjan Biswas

7,746
5
47
77

Selecting and Importing Data Correlating with Most Recent Date with Microsoft Query SQL

1 Answers1