How can I select ONLY the nearest value less-than the 'target' value?

Question

I'm trying to filter historic Betfair data. I want to get one record for each item in FULL_DESCRIPTION. The SQL I'm using gives me several records per item, each having a different LATEST_TAKEN. LATEST_TAKEN is already filtered to give values less than DT ACTUAL_OFF (both are date/time). I've looked at other postings but my capability levels don't match my enthusiasm.

I'm currently using:

SELECT Bfinf_other_150126to150201_150204124439.*
FROM Bfinf_other_150126to150201_150204124439
WHERE (((Bfinf_other_150126to150201_150204124439.FULL_DESCRIPTION) Like "English Soccer/Barclays Premier League*") 
    AND ((Bfinf_other_150126to150201_150204124439.LATEST_TAKEN) < [DT ACTUAL_OFF]) 
    AND ((Bfinf_other_150126to150201_150204124439.SPORTS_ID)="1") 
    AND ((Bfinf_other_150126to150201_150204124439.EVENT)="Correct Score") 
    AND ((Bfinf_other_150126to150201_150204124439.IN_PLAY)="pe"))
ORDER BY Bfinf_other_150126to150201_150204124439.EVENT_ID, Bfinf_other_150126to150201_150204124439.FULL_DESCRIPTION, Bfinf_other_150126to150201_150204124439.SELECTION, Bfinf_other_150126to150201_150204124439.LATEST_TAKEN DESC;

This leaves me with multiple records per item whereas I only want the record whose LATEST_TAKEN value is closest to that of DT ACTUAL_OFF

score 0 · Answer 1 · edited Feb 15 '15 at 09:46

So taking your query, I just changed the order in the ORDER BY clause so LATEST_TAKEN was the first sort parameter. Then I added a LIMIT 1 so it only returned one result. I think this should return what you're looking for

SELECT
    Bfinf_other_150126to150201_150204124439.*
FROM
    Bfinf_other_150126to150201_150204124439
WHERE
    (((Bfinf_other_150126to150201_150204124439.FULL_DESCRIPTION) Like "English Soccer/Barclays Premier League*") AND
    ((Bfinf_other_150126to150201_150204124439.LATEST_TAKEN)<[DT ACTUAL_OFF]) AND
    ((Bfinf_other_150126to150201_150204124439.SPORTS_ID)="1") AND
    ((Bfinf_other_150126to150201_150204124439.EVENT)="Correct Score") AND
    ((Bfinf_other_150126to150201_150204124439.IN_PLAY)="pe"))
ORDER BY
    Bfinf_other_150126to150201_150204124439.LATEST_TAKEN DESC,
    Bfinf_other_150126to150201_150204124439.EVENT_ID,
    Bfinf_other_150126to150201_150204124439.FULL_DESCRIPTION,
    Bfinf_other_150126to150201_150204124439.SELECTION
LIMIT 1;

I have simplified matters for clarity:

SELECT   
Bfinf_other_150126to150201_150204124439.FULL_DESCRIPTION, Bfinf_other_150126to150201_150204124439.SELECTION, Bfinf_other_150126to150201_150204124439.ODDS, Bfinf_other_150126to150201_150204124439.LATEST_TAKEN
FROM Bfinf_other_150126to150201_150204124439   
WHERE   
(((Bfinf_other_150126to150201_150204124439.SPORTS_ID)="1") AND ((Bfinf_other_150126to150201_150204124439.EVENT)="Correct Score") AND ((Bfinf_other_150126to150201_150204124439.IN_PLAY)="pe") AND ((Bfinf_other_150126to150201_150204124439.FULL_DESCRIPTION) Like "English Soccer/Barclays Premier League*") AND ((Bfinf_other_150126to150201_150204124439.LATEST_TAKEN)<[DT ACTUAL_OFF]))  
ORDER BY    
Bfinf_other_150126to150201_150204124439.EVENT_ID, Bfinf_other_150126to150201_150204124439.FULL_DESCRIPTION, Bfinf_other_150126to150201_150204124439.SELECTION, Bfinf_other_150126to150201_150204124439.LATEST_TAKEN DESC;

Gives:

FULL_DESCRIPTION - SELECTION - ODDS - LATEST_TAKEN

English Soccer/Barclays Premier League/Fixtures 31 January /Hull v Newcastle 0 - 0 9.2 31-01-2015 12:34:52

English Soccer/Barclays Premier League/Fixtures 31 January /Hull v Newcastle 0 - 0 9.8 28-01-2015 03:09:13

English Soccer/Barclays Premier League/Fixtures 31 January /Hull v Newcastle 0 - 0 10.5 25-01-2015 19:19:35

English Soccer/Barclays Premier League/Fixtures 31 January /Hull v Newcastle 0 - 0 9.4 24-01-2015 11:51:58

English Soccer/Barclays Premier League/Fixtures 31 January /Hull v Newcastle 0 - 1 9.2 31-01-2015 12:45:05

English Soccer/Barclays Premier League/Fixtures 31 January /Hull v Newcastle 0 - 1 9.6 31-01-2015 12:00:57

English Soccer/Barclays Premier League/Fixtures 31 January /Hull v Newcastle 0 - 1 9 31-01-2015 11:50:25

English Soccer/Barclays Premier League/Fixtures 31 January /Hull v Newcastle 0 - 1 8.8 30-01-2015 18:26:06

English Soccer/Barclays Premier League/Fixtures 31 January /Hull v Newcastle 0 - 2 16 31-01-2015 12:45:05

English Soccer/Barclays Premier League/Fixtures 31 January /Hull v Newcastle 0 - 2 16.5 31-01-2015 12:43:22

I require rows 1,5 and 9
I.E. For each match (FULL_DESCRIPTION) the correct score(SELECTION) ODDS at the greatest time (LATEST_TAKEN) before the start of the event ([DT ACTUAL_OFF).

Thanks Frank.This gives the error message:Syntax error(missing operator) in query expression 'Bfinf_other150126to150201_150204124439.SELECTION LIMIT !'. — Moosetud, Feb 14 '15 at 21:16
I think changing the ORDER BY clause will not work as FULL_DESCRIPTION has several SELECTION's and each SELECTION has it's own LATEST_TAKEN (which is date/time and must be the closest to DT ACTUAL_OFF) — Moosetud, Feb 14 '15 at 21:37
EVENT_ID DT ACTUAL_OFF FULL_DESCRIPTION SELECTION LATEST_TAKEN 1 9:00 RUN 1 8:55 1 9:00 RUN 2 8:50 1 9:00 RUN 3 8:00 2 10:30 Jump 1 10:29 2 10:30 Jump 5 10:20 2 10:30 Jump 4 10:15 My desired return is the first and fourth record. — Moosetud, Feb 14 '15 at 21:45
I think the answer lies along the lines of: http://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql?rq=1 So I'm playing around with that. — Moosetud, Feb 15 '15 at 11:29

How can I select ONLY the nearest value less-than the 'target' value?

1 Answers1