CODEWITHSUNDEEP
sqlmariadbgroupwise-maximum
UrukMorg
UrukMorg

Reputation: 7

Getting last_value MariaDB SQL

I have this data in the table:

internal_id match_id company_id market_id selection_id odds_value update_date
1442 8483075 66 1 1 100 2021-01-04 18:58:19
1 8483075 66 1 1 10 2021-01-04 18:57:19
2 8483075 66 1 2 19 2021-01-04 18:57:19
3 8483075 66 1 3 1.08 2021-01-04 18:57:19

I'm trying to get last value of odds_value from whole table for each combination of match_id + company_id + market_id + selection_id based on update_date.

I wrote this query which is not working:

SELECT
    odds.`internal_id`,
    odds.`match_id`,
    odds.`company_id`,
    odds.`market_id`,
    odds.`selection_id`,
    odds.`update_date`,
    odds.`odd_value`,
    LAST_VALUE (odds.`odd_value`) OVER (PARTITION BY odds.`internal_id`, odds.`match_id`, odds.`company_id`, odds.`market_id`, odds.`selection_id` ORDER BY odds.`update_date` DESC) AS last_value
FROM
    `odds`
LEFT JOIN `matches` ON matches.match_id = odds.match_id
WHERE
    odds.match_id = 8483075
    and odds.company_id = 66
GROUP BY
    odds.match_id,
    odds.company_id,
    odds.market_id,
    odds.selection_id

For match_id=8483075 & market_id=1 and selection_id=1 I'm getting odd_value 10 instead of 100. What am I doing wrong? or maybe there is a better way to get that (using internal_id = higher means most recent)?

Upvotes: 0

Views: 925

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

LAST_VALUE() is very strange. The problem is that the default window frame for the ordering is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

I won't go into the details, but the fix is to just always use FIRST_VALUE(). I'm also fixing the PARTITION BY to match the description in your question:

FIRST_VALUE(odds.odd_value) OVER (PARTITION BY odds.company_id, odds.market_id, odds.selection_id
                                  ORDER BY odds.update_date DESC
                                 ) AS last_value

Ironically, you already have a descending sort, so your last value was really fetching the first value anyway, sort of.

Upvotes: 1

Related Questions