检索最后(最新)不同的最高值 [英] Retrieve last (latest) distinct highest value

问题描述

我想从表tblquoteproposal`中为垂直的customerId检索最新的requestid，此处ID为3，在本示例中为ID 2& ID 4.

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客户ID 6 2 7 4 8 3 9 3

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

表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*

在此表中，requestid是外键. 还有另一个表tblrequest，该表具有requestid作为主键.

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 ;

推荐答案

为什么不尝试:

SELECT MAX(id)
FROM tblquoteproposal
GROUP BY requestid

并将此查询的结果提供给您所需的内容吗? (这可以是子查询).

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

例如，您的完整解决方案可能如下(我正在使用LEFT OUTER JOIN，因为您这样做了，我不确定这是正确的方法，也许INNER JOIN更合适):<​​/p>

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 )

这篇关于检索最后(最新)不同的最高值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！