how to select top 1 from a table like in sqlserver

Just like the question, I want to get the first row of the table

select top 1 * from tableA order by update_time;

but the squirrel shows the error  wrong syntax,  rownumber= 1 can not work too.

How to get the first row  ?


  • Options
    Try this:
    SELECT * FROM MyTableName
    ORDER BY 1
    LIMIT X;
     X is the number of rows you want to limit in the search results
  • Options
    Navin_CNavin_C Vertica Customer
    Hi Lan,

    The best way to do this is given by Benny, but there are other alternatives, in case you want to try

    Suppose I have a table like this.
    nnani=> select * from SEQUENCE_TEST; 
      1 | SA
      3 | EU
      2 | LA
    (3 rows)
    1, Using max function
    nnani=> select max(ID) from SEQUENCE_TEST; 
    (1 row)
    2. Using ROW_NUMBER
    nnani=> select sub.ID from (select ID,ROW_NUMBER() OVER (ORDER BY ID DESC) as RANK from SEQUENCE_TEST) sub where sub.rank = 1; 
    (1 row)
    So there are many ways you can achieve this.

    Best way is to use a LIMIT function .
    Also , just have a look at OFFSET function

  • Options
    Thank you Benny, helps me a lot
  • Options
    Thank you , Navin . First thing after my work is done is   to go through the manual books to learn the syntax  of vertica

Leave a Comment

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