CODEWITHSUNDEEP
mysql
PoorGrammer
PoorGrammer

Reputation: 47

Mysql Fetching rows base on date

Here's the code:

SELECT  RD.INTREQDQUANTITY, I.strItemName, v.strVendName, iu.strItemUnitName,  
          ip.dblItemPAmount, MAX(ip.dtmItemPasOf) AS EXR 
FROM TBLREQUESTDETAILS RD,tblitem I,tblvendor v,tblitemunit iu,tblitemprice ip` 
WHERE RD.strReqDItemCode = I.strItemCode 
AND RD.strReqDItemUnitCode = iu.strItemUnitCode 
AND RD.strReqDVendCode = v.strVendCode 
AND i.strItemCode = ip.strItemPItemCode 
and RD.strReqDReqHCode = 'RQST121' 
GROUP BY RD.INTREQDQUANTITY,I.strItemName,v.strVendName,iu.strItemUnitName, ip.dblItemPAmount
ORDER BY EXR desc ;

AND Here's The result: enter image description here

What should I do If I want to fetch the current price for each itemname,vendorname and itemunit?? I want to fetch only those rows who's price is the Latest... Help me please those with boxes are the rows that i want to fetch

Upvotes: 1

Views: 47

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

You can use where in the grouped value (and use explicict join notatio)

SELECT  RD.INTREQDQUANTITY, I.strItemName, v.strVendName, iu.strItemUnitName,  
          ip.dblItemPAmount, ip.dtmItemPasOf AS EXR 
FROM TBLREQUESTDETAILS RD
INNER JOIN tblitem I ON RD.strReqDItemCode = I.strItemCode 
INNER JOIN tblvendor v ON D.strReqDVendCode = v.strVendCode 
INNER JOIN tblitemunit iu ON RD.strReqDItemUnitCode = iu.strItemUnitCode 
INNER JOIN tblitemprice ip ON i.strItemCode = ip.strItemPItemCode 
WHERE RD.strReqDReqHCode = 'RQST121' 
and ( RD.INTREQDQUANTITY, I.strItemName, v.strVendName, iu.strItemUnitName, ip.dtmItemPasOf) 

    in ( SELECT  RD.INTREQDQUANTITY, I.strItemName, v.strVendName, iu.strItemUnitName,  
          MAX(ip.dtmItemPasOf) 
        FROM TBLREQUESTDETAILS RD
        INNER JOIN tblitem I ON RD.strReqDItemCode = I.strItemCode 
        INNER JOIN tblvendor v ON D.strReqDVendCode = v.strVendCode 
        INNER JOIN tblitemunit iu ON RD.strReqDItemUnitCode = iu.strItemUnitCode 
        INNER JOIN tblitemprice ip ON i.strItemCode = ip.strItemPItemCode 
        WHERE RD.strReqDReqHCode = 'RQST121'  
        GROUP BY RD.INTREQDQUANTITY,I.strItemName,v.strVendName,iu.strItemUnitName 
    )
 ORDER BY EXR desc ;

(and use explicict join notation .. i think is more readable)

Upvotes: 1

Related Questions