CODEWITHSUNDEEP
mysqlsqlconditional-statements
Spikist
Spikist

Reputation: 49

SQL SELECT last entry based on several conditions

In my case, I would like to select the last record for each couple (company_id, product_id) and return only those for which stock is not 0.

company_id  product_id    stock        date
    165         6           0       2017-01-09
    168         4         900000    2017-01-08
    165         6          1520     2017-01-07
    165         5        1200000    2017-01-06
    167         2           0       2017-01-05
    167         1        1401700    2017-01-04
    167         1        1450800    2017-01-03
    168         4           0       2017-01-02
    167         2        2360400    2017-01-01

In this example, the table would be:

company_id  product_id    stock        date
    168         4         900000    2017-01-08
    165         5        1200000    2017-01-06
    167         1        1401700    2017-01-04

Thanks !

Upvotes: 1

Views: 143

Answers (2)

Disillusioned
Disillusioned

Reputation: 14832

  • Use a subquery to get the max date per company/product.
  • Then join back to source table and filter out zero stock rows.

Query

select  t1.company_id, t1.product_id, t1.stock, t1.date
from    tablename t1
        inner join (
            select  company_id, product_id, max(date) maxdate
            from    tablename
            group by company_id, product_id) t2
        on  t1.company_id = t2.company_id
        and t1.product_id = t2.product_id
        and t1.date = t2.maxdate
where t1.stock <> 0

Upvotes: 2

jarlh
jarlh

Reputation: 44696

Have a sub-query that returns each company_id/product_id with its last date (where stock is non-zero.) JOIN with that result:

select t1.company_id, t1.product_id, t1.stock, t1.date
from tablename t1
join (select company_id, product_id, max(date) maxdate
      from tablename
      where stock <> 0 group by company_id, product_id) t2
  on  t1.company_id = t2.company_id
  and t1.product_id = t2.product_id
  and t1.date = t2.maxdate
where stock <> 0

Upvotes: 3

Related Questions