how to select top 1 from a table like in sqlserver

Lan_1

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  ?

Benny_Elgazar

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_C

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

Lan_1

Thank you Benny, helps me a lot

Lan_1

Thank you , Navin . First thing after my work is done is   to go through the manual books to learn the syntax  of vertica