We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


how to select top 1 from a table like in sqlserver — Vertica Forum

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  ?

Comments

  • Try this:
    SELECT * FROM MyTableName
    ORDER BY 1
    LIMIT X;
     X is the number of rows you want to limit in the search results
  • 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; 
    ID | LOCATION
    ----+----------
      1 | SA
      3 | EU
      2 | LA
    (3 rows)
    1, Using max function
    nnani=> select max(ID) from SEQUENCE_TEST; 
    max
    -----
       3
    (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; 
    ID
    ----
      3
    (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



  • Thank you Benny, helps me a lot
  • 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
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file