CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
Cornwell
Cornwell

Reputation: 3410

Get latest row on selecting multiple tables

I have this query:

SELECT DISTINCT 
    enc.id, enc.cus_id, enc.createdon, enc.status, enc.segment, enc_task.orderid, 
    enc_task.taskid, enc.currentstep, enc.groupid, enc.fdprotocol, 
    enc_task.linkfile, cus.fname, cus.lname, login.first_name, login.last_name, 
    login.username, login.user_code, login.id as assigned_id, fp.protocol, 
    init.typename, table4.allowtotal 
FROM 
    mob_encounters enc 
    JOIN mob_encounters_task enc_task ON enc_task.encounterid=enc.id
    JOIN mob_customer cus ON cus.id=enc_task.cus_id
    JOIN mob_login login ON login.id=enc.createdby
    LEFT JOIN mob_protocol_type fp ON fp.id=enc.fdprotocol
    LEFT JOIN initiation_type init ON init.id=enc.groupid
    LEFT JOIN mob_table4 table4 ON table4.encid=enc.id

GROUP BY 
    enc.id

The query is working, except I need it to return the latest row of enc_task.encounterid when doing this match: enc_task.encounterid = enc.id. Is it possible?

Upvotes: 0

Views: 113

Answers (1)

Valkyrie
Valkyrie

Reputation: 12045

A subquery might do what you're looking for:

SELECT DISTINCT 
                enc.id, enc.cus_id, enc.createdon, enc.status, enc.segment, enc_task.orderid, enc_task.taskid,
                enc.currentstep, enc.groupid, enc.fdprotocol, enc_task.linkfile, cus.fname, 
                cus.lname, login.first_name, login.last_name, login.username, login.user_code,
                login.id as assigned_id, fp.protocol, init.typename, table4.allowtotal 
            FROM 
                mob_encounters enc 
            JOIN 
                (select encounterid, orderid, taskid, linkfile from mob_encounters_task order by [FIELD THAT DESIGNATES LATEST VALUE] desc) AS enc_task ON enc_task.encounterid=enc.id
            JOIN 
                mob_customer cus ON cus.id=enc_task.cus_id
            JOIN 
                mob_login login ON login.id=enc.createdby
            LEFT JOIN 
                mob_protocol_type fp ON fp.id=enc.fdprotocol
            LEFT JOIN 
                initiation_type init ON init.id=enc.groupid
            LEFT JOIN 
                mob_table4 table4 ON table4.encid=enc.id
            GROUP BY 
                enc.id

Upvotes: 1

Related Questions