how to get a correlated subquery not supported by Vertica?

Mark_Andrews

I have a case where i need to perform a correlated subquery expression which works under postgres and sql server, but not under Vertica the query is supposed to return from each set of distinct fld1 the value of the primary key pkey for which fld2 is maximum CREATE TABLE test1(pkey INT, fld1 INT, fld2 INT); INSERT INTO test1 (pkey, fld1, fld2) VALUES (1,1,17); INSERT INTO test1 (pkey, fld1, fld2) VALUES (2,2,3); INSERT INTO test1 (pkey, fld1, fld2) VALUES (3,1,15); INSERT INTO test1 (pkey, fld1, fld2) VALUES (4,2,8); INSERT INTO test1 (pkey, fld1, fld2) VALUES (5,1,21); SELECT pkey FROM test1 AS t1 WHERE pkey = (SELECT pkey FROM test1 WHERE fld1 = t1.fld1 ORDER BY fld2 DESC LIMIT 1); I would expect the values to be returned as: 5 4 but i am getting: ERROR 2786: Correlated subquery expression without aggregates and with limit is not supported I have tried a group by with aggregate, but that is not supported in a Correlated subquery either. How can I accomplish the query I want with Vertica?

Mark_Andrews

SELECT pkey FROM test1 AS t1 WHERE fld2 = (SELECT MAX(fld2) FROM test1 WHERE fld1 = t1.fld1); seems to work.

[Deleted User]

Hi Mark! Thanks for you question and we're glad you figured it out! Let us know if there is anything else we can give you a hand with.