Friday, March 23, 2012

I cant do a Min() Group By?

I can't beleive it i'm supose to be a DB programmer and i can't think how to do this min() group by:

I need the return the SupID only, for each of the cheapest 'QFProductRef'
Ie the result should be (1,6,3,4)

table is called supplierInventory
SupIDQFProductRefDLRPriceAfterDisc
160138355.41
2601139416.12
3601387411.03
4600885312.25
5601383512.09
660113947.11
7601387412.81
8600885314.22

I'll have to go back to being a lumber jack if someone help!.
thanks in advancecan you xplain a little more...|||This query returns 4,6,1,3. If there were two or more records that tie for the lowest price then it would return each of their SupIDs.


Select supplierInventory.SupID
FROM
(Select QFProductRef, Min(DLRPriceAfterDisc ) as DLRPriceAfterDisc
From supplierInventory
Group By QFProductRef
) A
INNER JOIN supplierInventory ON A.QFProductRef=supplierInventory.QFProductRef AND A.DLRPriceAfterDisc=supplierInventory.DLRPriceAfterDisc

No comments:

Post a Comment