Retrieve last (latest) distinct highest value

Question

I want to retrieve the most recent requestid from table tblquoteproposal` for perticular customerId here 3, in this example ID 2 & ID 4.

table tblrequest requestid Customerid 6 2 7 4 8 3 9 3

Table tblquoteproposal

 id requestid QuotePraposalLink comment
 1  6          jgj               mghm   
 2  7         jhgj               hjgj  
 3  8         xyz1              rifsf
*4  8         xyz2              ri2sf*
 5  9         xyz3              ri3sf
*6  9         xyz4              ri4sf*

In this table requestid is foreign key. There is also another table tblrequest which has requestid as primary key.

I have written the following query but it doesn't give me the right results:

SELECT r.RequestId,r.ConsultantId,(SELECT concat(FirstName,' ',LastName) 
                                   FROM tbluser 
                                   WHERE UserId = v_userId) as "Customer",
       r.RequestDate,r.QuoteDetailsFileLink,r.Requestcomment,r.CustomerId,
       qp.QuotePraposalLink,qp.Comment
FROM tblrequest r
LEFT OUTER JOIN tblquoteproposal qp ON r.RequestId=qp.RequestId 
WHERE r.customerid=v_userId 
GROUP BY r.RequestId 
ORDER BY qp.id ;

Answer 1

Why not try:

SELECT MAX(id)
FROM tblquoteproposal
GROUP BY requestid

And feed the results of this query to whatever you need? (This can be a subquery).

For example, your complete solution may be as follows (I'm using LEFT OUTER JOIN because you did so, I'm not sure it's the right way, maybe INNER JOIN is more suitable):

SELECT ... your fields ...
FROM 
    tblquoteproposal p LEFT OUTER JOIN tblrequest r 
        on p.requestid = r.requestid
WHERE p.id IN (
    SELECT MAX(id)
    FROM tblquoteproposal
    GROUP BY requestid )

Answer 2

SELECT  *
FROM    (
        SELECT  requestid, MAX(id) AS mid
        FROM    tblquoteproposal
        GROUP BY
                requestid
        ) m
JOIN    tblrequest tr
ON      tr.id = tm.requestid
JOIN    tblquoteproposal tqp
ON      tqp.id = m.mid

Answer 3

An optimized version of Rax Olgud's answer could be:

SELECT ... your fields ...
FROM 
    tblquoteproposal p LEFT OUTER JOIN tblrequest r 
        ON p.requestid = r.requestid
WHERE p.id = ifnull( (
    SELECT MAX(id)
    FROM tblquoteproposal
    where requestid = r.requestid ), p.id )

Edited to make it an outer join.