Only show latest date from duplicate rows

Question

I'm trying to get the latest odds just before kick off for football games using Betfair's historical data. My database looks as follows:

Current DB

Now Betfair's historical data records the odds many times a day and therefore has lots of duplicate data. I have so far managed to only get all the odds for the selected Correct Score markets before kickoff.

As you can see there are duplicate records but I want to be able to only get the latest (max) time for each of the Correct Scores. I have highlighted the rows that I want the final query to only output.

My SQL statement is as follows:

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

I've tried to GROUP BY "FULL_DESCRIPTION" and "SELECTION" but nothing seems to be happening.

Any help would be greatly appreciated thanks.

Michael

The general GROUP BY rule says: If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function! — jarlh, Oct 31 '16 at 14:12
Very odd table name, `bfinf_other_161017to161023_161026123339`. Do you have different tables for different dates? How come? — jarlh, Oct 31 '16 at 14:13

score 0 · Answer 1 · answered Oct 31 '16 at 14:14

You can use a filter based on in clause and 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

Don't use double quote (if you need use backticks)

score 0 · Answer 2 · answered Oct 31 '16 at 14:16

Add a Where clause predicate that restricts output to the row with the latest datetime. Use subquery to determine the latest datetime for each selection.

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

Only show latest date from duplicate rows

2 Answers2