SQLite Subquery Syntax Error when using Multiple Columns

Question

It seems so simple; but I can't get this working.

select ISIN, QuoteTimestamp
from QuoteData
where (ISIN, QuoteTimestamp) IN
      (select ISIN, MAX(QuoteTimestamp) QuoteTimestamp
       from QuoteData
       group by ISIN)

This query gives me a syntax error. If I amend it to only use a single column (remove the MAX(QuoteTimewstamp)) it works.

It seems to be correct, as per the documentation.

Adding "AS" for the subquery alias makes no difference.

Here's the full error message from SQLite Manager.

SQLiteManager: Likely SQL syntax error: select ISIN, QuoteTimestamp
from QuoteData
where (ISIN, QuoteTimestamp) IN
       (select ISIN, MAX(QuoteTimestamp) AS QuoteTimestamp
        from QuoteData
        group by ISIN)

 [ near ",": syntax error ]
Exception Name: NS_ERROR_FAILURE
Exception Message: Component returned failure code: 0x80004005 (NS_ERROR_FAILURE) [mozIStorageConnection.createStatement]

This seems to work although, to me, it seems counter-intuitive. It is returning "Symbol", "Bid" and "Ask" from the row with MAX(QuoteTimestamp).

select ISIN, Symbol, Bid, Ask, MAX(QuoteTimestamp)
from QuoteData
group by ISIN

I'm sure Oracle and their ilk would tell me that Symbol, Bid and Ask are not group-by expressions.

Thanks for all the help.

Thanks, Al.

Hi,I get the same result when using "AS". Here's the full error. SQLiteManager: Likely SQL syntax error: select ISIN, QuoteTimestamp from QuoteData where (ISIN, QuoteTimestamp) IN (select ISIN, MAX(QuoteTimestamp) AS QuoteTimestamp from QuoteData group by ISIN) [ near ",": syntax error ] Exception Name: NS_ERROR_FAILURE Exception Message: Component returned failure code: 0x80004005 (NS_ERROR_FAILURE) [mozIStorageConnection.createStatement] — user4704286
– user4704286, Commented Mar 31, 2015 at 6:41
Please add the message to your question using the edit function. — Jens
– Jens, Commented Mar 31, 2015 at 6:48
Please show the part of the documentation that allows multi-valued expressions. — CL.
– CL., Commented Mar 31, 2015 at 7:29
OK. I must have assumed that you could specify multiple columns as you can with other DBs. Thanks for the comments. — user4704286
– user4704286, Commented Mar 31, 2015 at 7:39

CL. · Accepted Answer · 2015-03-31 07:34:27Z

0

In SQLite 3.7.11 or later, you do not need to use a subquery at all:

select ISIN, MAX(QuoteTimestamp) QuoteTimestamp
from QuoteData
group by ISIN

answered Mar 31, 2015 at 7:34

CL.

181k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user4704286 Over a year ago

Interesting. I did not think I could do this as I wanted to select other columns, and for those to be from the row with MAX(QuoteTimestamp) and I thought it would complain about non-grouping expressions. I shall have a play around with this. I hadn't shown my other columns, for brevity. Now I understand that you can't specify multiple columns, all becomes more clear. Thanks.

Collectives™ on Stack Overflow

SQLite Subquery Syntax Error when using Multiple Columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related