0
good afternoon... I need a help from the noble programmers of this forum to solve a small bug (I say I’m not a programmer, I’m curious). I have 2 databases, the first database is dynamic, it receives new data every 1 minute (ie every 1 minute 283 new lines enter the database) and a second fixed database with 203 lines (this database is static, will not be updated).
Anyway, I did a query that makes the Inner Join, but it is searching the wrong data from the database, it shows some right columns, but the part that comes from the dynamic database is not correct, is coming values from 2 days ago, and should return only the last time-based record corresponding to the Asset column of the fixed bank, that is, the query will always have 203 rows...
This is the query I’m using:
select a.Asset, a.FM, a.BANDASUP1, a.BANDAINF1, a.BANDASUP2, a.BANDAINF2, a.RL, a.FORCE, a.IBOV, a.ST, b.SYMBOL,b.DATE, b.TIME, b.LAST, b.VOLUME, b.PROJECTED_VOLUME, b.TRUE_RANGE, b.VWAP, b.DATETIME
from rtd.bdfixoteste2 as a
inner join (select max(DATEOK) as DATETIME, SYMBOL, DATE, LAST, TIME, VOLUME, PROJECTED_VOLUME, TRUE_RANGE, VWAP
from rtd.profitchart_01m_cleanf
WHERE DATEOK >= CURDATE()
group by LAST
) as b
On (b.SYMBOL = a.Asset) ORDER BY DATETIME DESC
Would you be so kind as to help me? To clarify, I wanted this query to always return the last dynamic database records based on the Asset column.
To complete, the first image refers to the fixed table (bdfixoteste2) and the second image refers to the dynamic table (profitchart_01m_cleanf) both are inside the rtd database.
In your subselect you are doing a GROUP BY only by the LAST field when you should do by all fields that are not in any aggregation function, in which case it should be: GROUP BY SYMBOL, DATE, LAST, TIME, VOLUME, PROJECTED_VOLUME, TRUE_RANGE, VWAP. You could omit some field as long as there is a functional dependency of the omitted field for any of the fields of the GROUP BY clause. I’m not sure that’s what you want, maybe it takes more than one subselect.
– anonimo
Thanks for the help, I added this condition in group by but now it is returning 36328 lines, where only the 203 records that are in the fixed database should come.
– Renato Lemos
It gets a little difficult to opine without knowing the sifnified fields.Perhaps the fields in your subselect should be limited to
max(DATEOK) as DATETIME, SYMBOL. and withGROUP BY SYMBOL.– anonimo
I fixed a solution, but now I have an ambiguity error (Error Code: 1052. Column 'SYMBOL' in field list is ambiguous ) columns, what is wrong now? SELECT r.* FROM rtd.profitchart_01m_cleanf r INNER JOIN (SELECT SYMBOL, DATE, MAX(TIME) FROM rtd.profitchart_01m_cleanf t JOIN INNER (SELECT SYMBOL, MAX(DATE) FROM rtd.profitchart_01m_cleanf GROUP BY SYMBOL) d ON d.SYMBOL = t.SYMBOL AND d.DATE = t.DATE GROUP BY t.SYMBOL, t.DATE) f ON f.SYMBOL = r.SYMBOL AND f.DATE = r.DATE AND f.TIME = r.TIME
– Renato Lemos