how to get a correlated subquery not supported by Vertica?

Mark_AndrewsMark_Andrews Registered User
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?

Comments

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file