Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

how to get a correlated subquery not supported by Vertica?

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

  • SELECT pkey FROM test1 AS t1 WHERE fld2 = (SELECT MAX(fld2) FROM test1 WHERE fld1 = t1.fld1); seems to work.
  • 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.

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.