仅显示重复行中的最新日期

Question

我想在橄榄球比赛开球前用必发的历史数据来获取最新的赔率。我的数据库如下所示：

Current DB

现在Betfair的历史数据一天多次记录赔率，因此有很多重复数据。到目前为止，我只能在开球前获得所选正确得分市场的所有赔率。

正如您所看到的，有重复的记录，但我希望能够仅获得每个正确分数的最新(最大)时间。我已经突出显示了我希望最终查询仅输出的行。

我的SQL语句如下：

SELECT "SCHEDULED_OFF", "FULL_DESCRIPTION", "EVENT", 
   "SELECTION_ID", "SELECTION", "ODDS", "WIN_FLAG", "LATEST_TAKEN"
FROM "bfinf_other_161017to161023_161026123339"
WHERE "FULL_DESCRIPTION" LIKE 'English Soccer/English Premier League%' 
    AND "EVENT" = "Correct Score" 
    AND "LATEST_TAKEN" < "SCHEDULED_OFF"
ORDER BY "FULL_DESCRIPTION" ASC, "SELECTION" ASC, "LATEST_TAKEN" DESC

我试着按"FULL_DESCRIPTION“和"SELECTION”分组，但似乎什么也没发生。

任何帮助都将不胜感谢。

迈克尔

Answer 1

您可以使用基于in子句和subselect with max LATEST_TAKEN group by SELECTION_ID的过滤器

SELECT `SCHEDULED_OFF`, `FULL_DESCRIPTION`, `EVENT`, `SELECTION_ID`, `SELECTION`, `ODDS`, `WIN_FLAG`, `LATEST_TAKEN`
FROM `bfinf_other_161017to161023_161026123339`
WHERE `FULL_DESCRIPTION` LIKE 'English Soccer/English Premier League%' 
AND `EVENT` = `Correct Score` 
AND `LATEST_TAKEN` IN  ( select MAX(LATEST_TAKEN`  
                       from `bfinf_other_161017to161023_161026123339` 
                       group by `SELECTION_ID`)
ORDER BY `FULL_DESCRIPTION` ASC, `SELECTION` ASC, `LATEST_TAKEN` DESC

不要使用双引号(如果需要使用反引号)

Answer 2

添加Where子句谓词，将输出限制为具有最新日期时间的行。使用子查询来确定每个选择的最新日期时间。

SELECT "SCHEDULED_OFF", "FULL_DESCRIPTION", "EVENT", 
    "SELECTION_ID", "SELECTION", "ODDS", "WIN_FLAG", "LATEST_TAKEN"
FROM "bfinf_other_161017to161023_161026123339" a
WHERE "FULL_DESCRIPTION" LIKE 'English Soccer/English Premier League%' 
   AND "EVENT" = "Correct Score" 
   AND "LATEST_TAKEN" < "SCHEDULED_OFF"
   and LATEST_TAKEN = 
      (Select Max(LATEST_TAKEN) 
       From bfinf_other_161017to161023_161026123339
       Where Selection_Id = a.Selection_Id)
ORDER BY "FULL_DESCRIPTION" ASC, "SELECTION" ASC, "LATEST_TAKEN" DESC