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

Issue with subquery and select Top 1 statement in it

Vertica doesn't seem to support a statement like the one below:

        update upatable set Fcst_QTR=(select top 1 qtr_fcst from   fivec_qtr a where
            a.GBU=upatable.GBU and
            a.Occurence=upatable.Occurence and


I have tried using LIMIT but it is also not allowed by Vertica in a subquery. Any thoughts, ideas and help is welcome! Thank  you!


  • Hi


    Try the below method :


    update upatable set Fcst_QTR=(select qtr_fcst from (
    select qtr_fcst,row_number() over () as rn from fivec_qtr a where and
    a.GBU=upatable.GBU and
    a.Occurence=upatable.Occurence and
    ) as aa where rn=1)



  • Thank you Eli!

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
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.