Snowflake subquery

SELECT tr.ID , (SELECT te.ID FROM "Terminal" te WHERE te.TERMINALID = tr.TERMINALID ORDER BY te.EXPORT_DATE DESC LIMIT 1 ) AS the_id -- <<-- add an alias for the column FROM "Transaction" tr ; 

UPDATE:

• length for type varchar cannot exceed 10485760
• just use type varchar (or text) instead

Works here (with quoted identifiers):

CREATE TABLE "Transaction" ("ID" VARCHAR(123), "TERMINALID" VARCHAR(123)) ; CREATE TABLE "Terminal" ( "ID" VARCHAR(123), "TERMINALID" VARCHAR(123), "EXPORT_DATE" DATE); SELECT tr."ID" , (SELECT te."ID" FROM "Terminal" te WHERE te."TERMINALID" = tr."TERMINALID" ORDER BY te."EXPORT_DATE" DESC LIMIT 1) AS meuk FROM "Transaction" tr ; 

BONUS UPDATE: avoid the scalar subquery and use plain old NOT EXISTS(...) to obtain the record with the most recent date:

SELECT tr."ID" , te."ID" AS meuk FROM "Transaction" tr JOIN "Terminal" te ON te."TERMINALID" = tr."TERMINALID" AND NOT EXISTS ( SELECT * FROM "Terminal" nx WHERE nx."TERMINALID" = te."TERMINALID" AND nx."EXPORT_DATE" > te."EXPORT_DATE" ) ; 

So few years later (2022), some correlated subqueries are support, but not this one:

using this data:

WITH transaction(id, terminalid) AS ( SELECT * FROM VALUES (1,10), (2,11), (3,12) ), terminal(id, terminalid, export_date) AS ( SELECT * FROM VALUES (100, 10, '2022-03-18'::date), (101, 10, '2022-03-19'::date), (102, 11, '2022-03-20'::date), (103, 11, '2022-03-21'::date), (104, 11, '2022-03-22'::date), (105, 12, '2022-03-23'::date) ) 

So compared to Marcin's we can now use a QUALIFY to select only one value per terminalid in a single step:

WITH last_terminal as ( SELECT id, terminalid FROM terminal QUALIFY row_number() over(PARTITION BY terminalid ORDER BY export_date desc) = 1 ) SELECT tr.ID, te.id FROM transaction AS tr JOIN last_terminal AS te ON te.TERMINALID = tr.TERMINALID ORDER BY 1; 

giving:

and if you have multiple terminals per day, and terimal.id is incrementing number you could use:

QUALIFY row_number() over(PARTITION BY terminalid ORDER BY export_date desc, id desc) = 1 

Now if your table is not that larger, you can do the JOIN then prune via the QUALIFY, and avoid the CTE, but on large tables this is much less performant, so I would only use this form when doing ad-hoc queries, where swapping forms if viable if performance problems occur.

SELECT tr.ID, te.id FROM transaction AS tr JOIN terminal AS te ON te.TERMINALID = tr.TERMINALID QUALIFY row_number() over(PARTITION BY tr.id ORDER BY te.export_date desc, te.id desc) = 1 ORDER BY 1; 

This kind of subquery is currently not supported.

Working with subqueries - Limitations:

The only type of subquery that allows a LIMIT / FETCH clause is an uncorrelated scalar subquery. Also, because an uncorrelated scalar subquery returns only 1 row, the LIMIT clause has little or no practical value inside a subquery

Query in question is correlated subquery, thus the result.

SELECT tr.ID, (SELECT te.ID FROM "Terminal" te WHERE te.TERMINALID = tr.TERMINALID --correlation ORDER BY te.EXPORT_DATE DESC LIMIT 1) FROM "Transaction" tr;