I have a table like this:
id, customer_id, item_id, field, value
And here's some example data:
id, cid, iid, field, value
1, 100, 1000, 'rating', 10
2, 100, 1000, 'rating', 15
3, 100, 1000, 'worth', 20
4, 100, 1000, 'worth', 25
5, 100, 1100, 'rating', 35
6, 100, 1100, 'rating', 30
7, 100, 1100, 'worth', 35
8, 100, 1100, 'worth', 30
9, 200, 2000, 'rating', 20
10, 200, 2000, 'rating', 25
11, 200, 2000, 'worth', 40
12, 200, 2000, 'worth', 45
13, 200, 2100, 'rating', 30
14, 200, 2100, 'rating', 35
15, 200, 2100, 'worth', 50
16, 200, 2100, 'worth', 55
I've been trying to write a query that returns only the newest value for all fields of all items of certain customer.
For customer 100 it would return:
id, cid, iid, field, value
2, 100, 1000, 'rating', 15
4, 100, 1000, 'worth', 25
6, 100, 1100, 'rating', 30
8, 100, 1100, 'worth', 30
And for customer 200 it would return:
id, cid, iid, field, value
10, 200, 2000, 'rating', 25
12, 200, 2000, 'worth', 45
14, 200, 2100, 'rating', 35
16, 200, 2100, 'worth', 55
There can be unlimited amount of different fields and there can be an unlimited amount of values for each field of each item of each customer.
The value keeps changing hourly and I want the query to return only the newest value for all fields of all items of certain customer.
The cid can be limited with a basic WHERE cid = '1' but I have no idea how do I group the query to get one value for each field and for each item. I know I can get the newest by finding MAX(id) for each field but I have no idea how to do it with one query.
I could do this with multiple queries and some PHP but is it possible to do it with a single query?
