CODEWITHSUNDEEP
phpmysql
Obi Ik
Obi Ik

Reputation: 159

Mysql Select from table where a date is max for each unique location_id AND product

I have a table - records - that looks like this

id | location_id | product | stock | date  
===|=============|=========|=======|======
1  | Bakery 1    |  cake   |  21   | 2
2  | Bakery 1    |  bread  |  23   | 2
3  | Bakery 2    |  cake   |  21   | 2
4  | Bakery 2    |  bread  |  21   | 2
5  | Bakery 1    |  cake   |  21   | 3
6  | Bakery 1    |  bread  |  23   | 3
7  | Bakery 2    |  cake   |  21   | 3
8  | Bakery 2    |  bread  |  21   | 3
9  | Bakery 1    |  cake   |  21   | 4
10 | Bakery 1    |  bread  |  23   | 4
11 | Bakery 2    |  bread  |  23   | 4

For each location and for each product, i want to pick the row that has the highest date value. Which will look like ths

id | location_id | product | stock | date  
===|=============|=========|=======|======
7  | Bakery 2    |  cake   |  21   | 3
9  | Bakery 1    |  cake   |  21   | 4
10 | Bakery 1    |  bread  |  23   | 4
11 | Bakery 2    |  bread  |  23   | 4

How do i execute this with one query? I could loop through all the locations and all the products and build the queries but its more time and memory consuming?

Upvotes: 1

Views: 87

Answers (2)

O. Jones
O. Jones

Reputation: 108796

If the following conditions are true you can do this job very efficiently using the id column

  1. your id column is autoincrementing
  2. the date column represents the current date: that is, you never insert a backdated record.
  3. you don't UPDATE the date column

If these conditions are true, that means the unique identifier id can be used as a proxy for the non-unique value date for each record. A row with a higher date value is always guaranteed to have a higher id value.

If you can do this you can make your query perform very well.

First, you create a subquery to get the id of the latest row for each combination of location and product.

SELECT MAX(id)
  FROM records
 GROUP BY location_id, date

Then you use that set of id values to pull the correct records from your table

SELECT *
  FROM records
 WHERE id IN (
                SELECT MAX(id)
                 FROM records
                GROUP BY location_id, date
              )
ORDER BY location_id, product

Upvotes: 1

juergen d
juergen d

Reputation: 204854

select r1.*
from records r1
join
(
  select location_id, product, max(date) as date
  from records
  group by location_id, product
) r2 on r1.location_id = r2.location_id 
    and r1.product = r2.product
    and r1.date = r2.date

Upvotes: 2

Related Questions