MySQL 5.6.27: How to get latest pairs for different points in time in a single query? I use the following query to get pairs of latest s1.value for given group ids and some fixed date. Now if I want to query pairs for different dates I have to run this query several times with different dates. My question is can I rework this query so I could query value pairs for several dates at once?  

SELECT
    s1.group_id, s1.value, s1.ts_week_start
  FROM stats_kpi AS s1
  WHERE
    (SELECT COUNT(*) FROM stats_kpi as s2
       WHERE s2.group_id = s1.group_id
         AND s2.ts_week_start &gt;= s1.ts_week_start
         AND s2.ts_week_start &lt;= timestamp('2018-10-01')
    ) &lt;= 2
    AND s1.group_id IN (3939)
    AND s1.ts_week_start &lt;= timestamp('2018-10-01')
  ORDER BY s1.ts_week_start DESC;


UPD #1

Sample input:

group_id | value | ts_week_start
       1 |     1 | 2018-01-01 00:00:00
       1 |     2 | 2018-01-02 00:00:00
       1 |     3 | 2018-01-03 00:00:00
       1 |     4 | 2018-01-04 00:00:00
       1 |     5 | 2018-01-05 00:00:00
       2 |     1 | 2018-01-01 00:00:00
       2 |     2 | 2018-01-02 00:00:00
       2 |     3 | 2018-01-03 00:00:00
       2 |     4 | 2018-01-04 00:00:00
       2 |     5 | 2018-01-05 00:00:00


I want to get the following, for two groups(1, 2) and three dates (2018-01-05, 2018-01-02, 2018-01-01)
get pairs for latest and previous to latest values to the date, i.e. the desired result would be like:

group_id | value | ts_week_start
-- for group 1 and dates 2018-01-02/2018-01-01
       1 |     1 | 2018-01-01 00:00:00
       1 |     2 | 2018-01-02 00:00:00

-- for group 1 and date 2018-01-05
       1 |     4 | 2018-01-04 00:00:00
       1 |     5 | 2018-01-05 00:00:00

-- for group 2 and dates 2018-01-02/2018-01-01
       2 |     1 | 2018-01-01 00:00:00
       2 |     2 | 2018-01-02 00:00:00

-- for group 2 and date 2018-01-05
       2 |     4 | 2018-01-04 00:00:00
       2 |     5 | 2018-01-05 00:00:00


For 2018-01-01 there's only one record in the result - latest one and no previous.

Schema and data:

CREATE TABLE `stats_kpi` (
  `group_id` bigint(20) NOT NULL,
  `value` double unsigned DEFAULT NULL,
  `ts_week_start` timestamp NULL DEFAULT CURRENT_TIMESTAMP
  )

INSERT INTO `stats_kpi` VALUES (1, 1, timestamp('2018-01-01'));
INSERT INTO `stats_kpi` VALUES (1, 2, timestamp('2018-01-02'));
INSERT INTO `stats_kpi` VALUES (1, 3, timestamp('2018-01-03'));
INSERT INTO `stats_kpi` VALUES (1, 4, timestamp('2018-01-04'));
INSERT INTO `stats_kpi` VALUES (1, 5, timestamp('2018-01-05'));
INSERT INTO `stats_kpi` VALUES (2, 1, timestamp('2018-01-01'));
INSERT INTO `stats_kpi` VALUES (2, 2, timestamp('2018-01-02'));
INSERT INTO `stats_kpi` VALUES (2, 3, timestamp('2018-01-03'));
INSERT INTO `stats_kpi` VALUES (2, 4, timestamp('2018-01-04'));
INSERT INTO `stats_kpi` VALUES (2, 5, timestamp('2018-01-05'));


UPD #2

I modified proposal of Kapil Bhagchandani so it's a single query now. What I don't like is the querying dates from the stats_kpi table while I know them upfront:

SELECT
DISTINCT s1.group_id, s1.value, s1.ts_week_start
FROM stats_kpi AS s1, 
  (SELECT DISTINCT(s3.ts_week_start) AS ts FROM stats_kpi AS s3
     WHERE s3.ts_week_start IN (TIMESTAMP('2018-01-05'), TIMESTAMP('2018-01-02'), TIMESTAMP('2018-01-01'))) AS dates
WHERE(
    SELECT COUNT(*) FROM stats_kpi as s2
    WHERE s2.group_id = s1.group_id
    AND s2.ts_week_start &gt;= s1.ts_week_start
    AND s2.ts_week_start &lt;= dates.ts
) &lt;= 2
AND s1.group_id IN (1,2)
AND s1.ts_week_start &lt;= dates.ts
ORDER BY group_id,value,s1.ts_week_start DESC;
SET @date:='2018-01-03';

select * from ( select ta.*,
                       if(@typex=ta.group_id, 
                          @rownum:=@rownum+1, 
                          @rownum:=1+least(0,@typex:=ta.group_id)) rown 
                from stats_kpi ta, (select @rownum:=1, @typex:='_') zz
                where ts_week_start &lt;= timestamp(@date)
                order by group_id, ts_week_start DESC
              ) yy
where rown &lt; 3


fiddle