In Google Sheets I previously successfully ran this query through Encoded query function:
SELECT Q, Y, J WHERE Y IS NOT NULL AND NOT Y LIKE '[%' ORDER BY Q, Y, J
Encoded query function for the above looks like this:
SELECT%20Q%2C%20Y%2C%20J%20WHERE%20Y%20IS%20NOT%20NULL%20AND%20NOT%20Y%20LIKE%20'%5B%25'%20ORDER%20BY%20Q%2C%20Y%2C%20J
All good. Now I want to strip out all results where the data is exactly the same across the row. In effect, stripping out duplicate rows. I can't seem to do this. This is where I got to. Query function:
=UNIQUE(QUERY(A2:Y3608,"SELECT Q, Y, J WHERE Y IS NOT NULL AND NOT Y LIKE '[%'ORDER BY Q, Y, J"))
When I run this as a url (using the encoded query function) I get the error:
{"version":"0.6","reqId":"0","status":"error","errors":[{"reason":"invalid_query","message":"INVALID_QUERY","detailed_message":"Invalid query: PARSE_ERROR: Encountered \u0022 \u003cID\u003e \u0022UNIQUE \u0022\u0022 at line 1, column 1.\nWas expecting one of:\n \u003cEOF\u003e \n \u0022select\u0022 ...\n \u0022where\u0022 ...\n \u0022group\u0022 ...\n \u0022pivot\u0022 ...\n \u0022order\u0022 ...\n \u0022skipping\u0022 ...\n \u0022limit\u0022 ...\n \u0022offset\u0022 ...\n \u0022label\u0022 ...\n \u0022format\u0022 ...\n \u0022options\u0022 ...\n "}]}
How do I get UNIQUE to work?
There is no distinct clause in the query language. One workaround would be use a group by clause, applying max() to aggregate some column that you can later discard, like this:
select Q, Y, J, max(Q) where Y is not null and not Y like '[%' group by Q, Y, J
Another workaround would be to insert a new tab in the spreadsheet and add a helper table there with the unique() function. The new tab needs to be moved so that it appears leftmost in the tab bar. You can then get the data from the new tab through your Data Source URL endpoint.