constructing a query for the following table

Question

can anyone generate a query for me. Lets say i have a table sales(saleID, date_of_sales, customerID, itemID, saleprice)

• date_of_sales is the datetime field which stores the time of the sale.

• customerID is self exlpaining tells to whom item was sold.

• itemID is ID of the item sold.

• saleprice is the price that the item was sold.

I want to construct a query which will give out the detail of the last purchase by each customers. this could be done by using date_of_sales.

Example table

saleID | date_of_sales | customerID | itemID | saleprice

101 | 2008-01-01 | C2000 | I200 | 650 |
102 | 2010-01-01 | C2000 | I333 | 200 |
103 | 2007-01-01 | C3333 | I111 | 800 |
104 | 2009-12-12 | C3333 | I222 | 100 |

this is the example data table, there are only two customer for simplicity.

• customer C2000 did his last purchase on 2010-01-01

• customer C3333 did his last purchase on 2009-12-12

I want to get a result like this

customerID | date_of_sales | itemID | saleprice

C2000 | 2010-01-01 | I333 | 200 |

C3333 | 2009-12-12 | I222 | 100 |

Answer 1

This might be what you are looking for...

SELECT *
    FROM sales
    WHERE sales.date_of_sales = (SELECT MAX(date_of_sales)
                                     FROM sales s2
                                     WHERE s2.customerID = sales.customerID);

There is a slight problem with it; if there were two sales on the same day to the same customer, you'll get two rows (unless your date-of-sales column includes the time as well). I think the same applies to the answer above, though.

Answer 2

Additionally, if you DO want to get results based on only a SINGLE entry of the maximum date, I would use the query by @Sachin Shanbhag above, but add a maximum sales ID value too... Since that would be implied as sequential, whichever was entered last would probably be the most recent.

SELECT S.* FROM   
    sales S   
    INNER JOIN   
    (   SELECT   
            customerID, 
            MAX(date_of_sales) dos,
            MAX(SalesID) maxSale
        FROM   
            sales    
        GROUP BY customerID  
    ) S2 ON S.customerID = S2.customerID 
          AND S.date_of_sales = S2.dos  
          AND S.SalesID = S2.maxSale