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.
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 |