Mysql Fetching rows base on date

Question

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:

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

I think you must remove RD.INTREQDQUANTITY from group by if you want the result for this combination (itemname,vendorname and itemunit) — Imanez
– Imanez, Commented Oct 15, 2016 at 7:17

ScaisEdge · Accepted Answer · 2016-10-15 13:00:42Z

1

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)

edited Oct 15, 2016 at 13:00

answered Oct 15, 2016 at 7:14

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

PoorGrammer Over a year ago

0 rows returned :(

PoorGrammer Over a year ago

I mean only returns the row with the highest dtmPAsOf

ScaisEdge Over a year ago

Looking to you sample seems which is the column you want .. ?

PoorGrammer Over a year ago

I want to fetch those that are inside the black box, green box and red box.Which are row which contains the latest price base on date

ScaisEdge Over a year ago

I'm sorry but The highest dtmPAsOf is not the latest date ... and the the related rows should contain the last price (alias dblItemPAmount) based on date ... ? ..

|

Collectives™ on Stack Overflow

Mysql Fetching rows base on date

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related