Getting latest quote data for stock

Question

I am collecting quote data and chose opt_ticker and quoteTimeStamp as the primary key so that I can store unique quotes over time. I now want to create a view where I can see the latest quote for each opt_ticker (data base has other opt_tickers with unique quotes as well). Basically want to see the latest quote for each stock/option.

In the example above, I want to get that last row as it is the latest timestamp for that particular contract.

I thought this query would do the trick but mysql complains that I need to do a group by.

select symbol,opt_ticker,ask,bid,exp,strike,type,max(quoteTimeStamp)
from optionquotes
group by opt_ticker

21:36:42    select symbol,opt_ticker,ask,bid,exp,strike,type,max(quoteTimeStamp) from optionquotes group by opt_ticker,symbol LIMIT 0, 1000 Error Code: 1055. Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'od2.optionquotes.ask' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by  0.000 sec

Here my server info if it helps

Server 
Product: (Ubuntu) 
Version: 5.7.30-0ubuntu0.16.04.1 
Connector 
Version: C++ 8.0.20

This sounds so easy but I am having the toughest time figuring this out, thank you in advance.

The Impaler · Answer 1 · 2020-06-13T04:46:45.047

2

In MySQL 5.x you can do:

select *
from optionquotes
where (opt_ticker, quoteTimeStamp) in (
  select opt_ticker, max(quoteTimeStamp)
  from optionquotes
  group by opt_ticker
)

In MySQL 8.x you can do:

select *
from (
  select *,
    row_number() over(partition by opt_ticker order by quoteTimeStamp desc) as rn
  from optionquotes
) x
where rn = 1

edited Jun 13 '20 at 04:46

answered Jun 13 '20 at 04:42

The Impaler

45,731
9
39
76

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(partition by opt_ticker order by quoteTimeStamp desc) as rn from optionquotes' at line 4 Getting the above error – nGX Jun 13 '20 at 04:46
1

@nGX Yes, just noticed you have MySQL 5.x. Please use the first query. – The Impaler Jun 13 '20 at 04:47
Like a knight in shining armor. You have solved something that I have been doing my best trying to figure out for the past few days. Thank you. Why is sql is complicated /s – nGX Jun 13 '20 at 04:50

score 1 · Answer 2 · answered Jun 13 '20 at 04:55

Just to round out the answers, here is a canonical way to do this using a join:

SELECT oq1.*
FROM optionquotes
INNER JOIN
(
    SELECT opt_ticker, MAX(quoteTimeStamp) AS maxQuoteTimeStamp
    FROM optionquotes
    GROUP BY opt_ticker
) oq2
    ON oq1.opt_ticker = oq2.opt_ticker AND
       oq1.quoteTimeStamp = oq2.maxQuoteTimeStamp;

Getting latest quote data for stock

2 Answers2