sqlite query based on selecting from column string and filtering by last rows in descending order-CodePudding

I have a database, tick_df, that looks like this

       timestamp                             time        symbol     price
0  1649320867903 2022-04-07 08:41:07.903000 00:00  LUNA/USD:USD  108.3220
1  1649320867884 2022-04-07 08:41:07.884000 00:00   SOL/USD:USD  115.9125
2  1649320867321 2022-04-07 08:41:07.321000 00:00  LUNA/USD:USD  108.3220
3  1649320866243 2022-04-07 08:41:06.243000 00:00  LUNA/USD:USD  108.3300
4  1649320866225 2022-04-07 08:41:06.225000 00:00  AVAX/USD:USD   84.6590
5  1649320866144 2022-04-07 08:41:06.144000 00:00  AVAX/USD:USD   84.6640

I am trying to select all columns and the last 2 rows in descending time order from only one symbol - e.g.AVAX/USD:USD. The query I have tried is

SELECT symbol FROM tick_df WHERE symbol LIKE AVAX% ORDER BY timestamp DESC LIMIT2

But this return an error

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "ORDER": syntax error

can anyone point to what I'm doing wrong here.

Thanks

CodePudding user response：

If you look at the error message it tells you that there is a syntax error near ORDER BY, it hints to an error immediately before that clause.

As you can read from the documentation:

A string constant is formed by enclosing the string in single quotes (').

You should enclose the LIKE arg in single quotes like this

SELECT symbol FROM tick_df WHERE symbol LIKE 'AVAX%' ORDER BY timestamp DESC LIMIT 2

Also, if you want to return all the column you should SELECT * and not SELECT symbol because the latter will only return the symbol column.

The final correct query should be

SELECT * FROM tick_df WHERE symbol LIKE 'AVAX%' ORDER BY timestamp DESC LIMIT 2