Member Avatar for karanits

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 |
  • 2 Contributors
  • 1 Reply
  • 112 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by pritaeas
Member Avatar for pritaeas 
SELECT customerID, date_of_sales, itemID, saleprice
FROM sales s1
WHERE date_of_sales = (SELECT MAX(date_of_sales) FROM sales s2 WHERE s1.customerID = s2.customerID)
Edited by pritaeas because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.