Microsoft Query SQL query question

Question

I am completely new to Microsoft Query.

I have the following tables defined:

FLEET_MASTERS Table        
- REG_NO
- FMS_ID

ODOMETERS Table                           
- ODO_ID 
- FMS_ID

ODOMETERS_READING Table
- ODO_ID          
- ODO_READING
- ODO_READING_DATE
- ODO_READING_TYPE

I need these information:

REG_NO, ODO_READING, max(ODO_READING DATE), ODO_READING_TYPE

But from one REG_NO, there may be multiple ODO_READING and ODO_READING DATE, I just need the ODO_READING with the most recent ODO_READING DATE.

Answer 1

Try:

select fm.REG_NO, odr.ODO_READING, odr.ODO_READING_DATE as MAX_READING_DATE, odr.ODO_READING_TYPE
from FLEET_MASTERS fm
inner join ODOMETERS od on fm.FMS_ID = od.FMS_ID
inner join  (
 select ODO_ID, max(odr.ODO_READING_DATE) as MAX_READING_DATE
 from ODOMETERS_READING
 group by ODO_ID
) odm
inner join ODOMETERS_READING odr on odm.ODO_ID = odr.ODO_ID and odm.MAX_READING_DATE = odr.ODO_READING

Answer 2

From your mess of text above I'm assuming the following schema

CREATE TABLE FLEET_MASTERS
(
    FMS_ID INT PRIMARY KEY,
    REG_NO VARCHAR(10)
)
CREATE TABLE ODOMETERS_READING
(
    OMR_ID INT PRIMARY KEY,
    FMS_ID INT,
    ODO_READING INT,
    ODO_READING_DATE DATETIME,
    ODO_READING_TYPE INT
)

Given that schema the following query would give you what you want, assuming there's only one record per Reg_No for a given date/time:

SELECT
    FM.REG_NO,
    OMR.ODO_READING,
    OMR.ODO_READING_DATE,
    OMR.ODO_READING_TYPE
FROM FLEET_MASTERS FM
INNER JOIN ODOMETERS_READING OMR
    ON FM.FMS_ID = OMR.FMS_ID
WHERE OMR.ODO_READING_DATE = (
    SELECT MAX(OMR1.ODO_READING_DATE)
    FROM ODOMETERS_READING OMR1
    WHERE OMR1.FMS_ID = FM.FMS_ID
)

I think I have that syntax right, all off the top of my head, no SQL server handy to test it.