CODEWITHSUNDEEP
mysqlsql
user3756488
user3756488

Reputation: 233

Fetch the latest information in Mysql

I have a below table in MySQL DB.

create table shops(
        id bigint(20) NOT NULL AUTO_INCREMENT,
        shopid bigint(10) NOT NULL,
        first_level_shop_id int(11) NOT NULL DEFAULT '0',
        start_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        rate int(11) NOT NULL DEFAULT 0,
        PRIMARY KEY (`id`),
        KEY shopid (shopid,first_level_shop_id,start_time)
        ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

I would like to fetch the rate of shop's all latest first_level_shop_id(need to consider start_time). The one shopid is having many first_level_shop_id. for example:

shopid,first_level_shop_id,rate,start_time
110,110001,20,2018-08-16 00:00:00
110,210011,50,2018-08-16 00:00:00

There are many first_level_shop_id under one shopid 110.I would like to fetch the latest one. Please help me to write the SELECT query for the same. Thank you.

Upvotes: 0

Views: 37

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You can use a correlated subquery:

select s.*
from shops s
where s.start_time = (select max(s2.start_time)
                      from shops s2
                      where s2.shopid = s.shopid
                     );

Upvotes: 2

Related Questions