CODEWITHSUNDEEP
mysqlsqljoinmariadb
Justin
Justin

Reputation: 141

How to use 'Mysql JOIN' with sql text of same table

I want to use mysql's "JOIN"

I want to group rows by "date_text" where "tokenIdx" is "1001" and "datetime_unix" is the highest value.

Is my code wrong?

 SELECT `A.idx` 
FROM `data_candle_h1` 'A'
JOIN 
    (
        SELECT `date_text`, MAX(`datetime_unix`) AS 'datetime_unix' 
        FROM `data_candle_h1` 
        WHERE `tokenIdx` = '1002' 
        GROUP BY `date_text`
    ) 'B'
ON `A.datetime_unix` = `B.datetime_unix` 
WHERE  `A.tokenIdx` = '1002'

database table

Upvotes: 0

Views: 28

Answers (1)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

Your query is syntactically perfect. Just remove single quotes('') around table aliases (A and B). I have corrected it. Please check this out.

SELECT `A.idx` 
FROM `data_candle_h1` A
JOIN 
    (
        SELECT `date_text`, MAX(`datetime_unix`) AS 'datetime_unix' 
        FROM `data_candle_h1` 
        WHERE `tokenIdx` = '1002' 
        GROUP BY `date_text`
    ) B
ON `A.datetime_unix` = `B.datetime_unix` 
WHERE  `A.tokenIdx` = '1002'

Upvotes: 1

Related Questions