Question 248935 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to query a row

I have the following table (orders)

orderID customerID (varchar) articleIDs (varchar) date (datetime)
1 c1 9012, 9011, 9013 2023-09-19T01:15
2 c1 9013 2023-08-12T09:35
3 c2 9052, 9053, 9054 2023-09-17T11:16
4 c3 9011, 9063 2023-09-12T03:24
5 c4 9011, 9033 2023-09-09T09:09
6 c4 9099, 9121, 9356 2023-09-17T08:55
7 c2 9012, 9011 2023-08-19T07:11
8 c5 9056, 9001, 9078 2023-09-19T06:25

Now I am trying to formulate a query that will answer the following: Which customer bought item x the last time?
For example, I want to know this for articleId 9011, my expectation is that I will get c1 2023-09-19T01:15 back.

select customerID, max(orderdate) as d
from orders
where articleIDs like '%9011%'
group by customerID
order by d desc
limit 1;

This query is doing what I want. But is there any other/better solution?

Tags:

3

Answers


  1. 0

    you can try something like this:

    select DISTINCT customerID, orderdate
from orders
where articleIDs like '%9011%'
order by d desc
limit 1;
    Login or Signup to reply.
  2. 0

    You can use the FIND_IN_SET function to search for values in a comma-separated string.

        SELECT customerID, MAX(orderdate) AS last_purchase_date
      FROM orders
     WHERE FIND_IN_SET('9011', REPLACE(articleIDs, ' ', '')) > 0
  GROUP BY customerID
  ORDER BY last_purchase_date DESC
     LIMIT 1;
    Login or Signup to reply.
  3. 0

    You can also try like this:

    SELECT customerID, MAX(date) AS max_date
FROM Orders
WHERE FIND_IN_SET('9011', REPLACE(articalIds, ' ', '')) > 0
GROUP BY customerID
ORDER BY max_date DESC
LIMIT 1;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search