0

I'm trying to get a mysql query to select each different item (id_product) in each category (id_type) that contains the most current date.

   id   id_type id_product      dt                price1     price2
2449712  11           50    2018-06-01 17:40:38 20281.10000 22157.40000
2449713  11           43    2018-06-04 12:17:06 1979.07000  1981.20000
2449714  11           46    2018-06-04 12:16:59 2071.18000  2075.02000
2449722  13           50    2018-06-04 00:00:00 26000.00000 
2449723  13           43    2018-06-04 00:00:00 2290.55000  
2449724  13           46    2018-06-04 00:00:00 2363.88000  
2458597  11           50    2018-06-04 13:25:13 20451.64000 22155.94000
2458598  11           43    2018-06-04 14:16:47 1988.92000  1991.05000
2458599  11           46    2018-06-04 14:16:51 2068.17000  2070.73000

The query result should be:

   id   id_type id_product      dt                price1     price2
2449722  13           50    2018-06-04 00:00:00 26000.00000 
2449723  13           43    2018-06-04 00:00:00 2290.55000  
2449724  13           46    2018-06-04 00:00:00 2363.88000  
2458597  11           50    2018-06-04 13:25:13 20451.64000 22155.94000
2458598  11           43    2018-06-04 14:16:47 1988.92000  1991.05000
2458599  11           46    2018-06-04 14:16:51 2068.17000  2070.73000

Thanks.

Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
crossmax
  • 346
  • 3
  • 20
  • Why are 'items' called 'products', and is 'category' called 'type', in the database? – KIKO Software Aug 05 '18 at 11:14
  • So, you need to delimit the distinct categories, then also get the latest date and THEN get the distinct id_products in those results. SELECT DISTINCT(id_product) FROM docs as d where (d.id_type) in (SELECT DISTINCT(id_type) FROM docs ) AND d.dt = (Select Max(dt) from docs); – CosminO Aug 05 '18 at 12:09
  • @CosminO DISTINCT is not a function. – Strawberry Aug 05 '18 at 12:17
  • @Strawberry Thanks for the link. I've already solved it by reading the answers. I'm sorry for duplicating – crossmax Aug 05 '18 at 14:57

0 Answers0