CODEWITHSUNDEEP
varcharvertica
Amit
Amit

Reputation: 20456

How to order by a varchar column numerically in vertica database?

How to order by a varchar column numerically in vertica database?

For example we can add a +0 in the order by clause in oracle to sort a varchar column numerically.

Thanks!

Upvotes: 1

Views: 1586

Answers (3)

serbaut
serbaut

Reputation: 5960

Use cast as in

select x from foo order by cast(x as int);

You will get an error if not all values can be casted to an int.

Upvotes: 2

Joe
Joe

Reputation: 200

If the data is truly numeric data, the '+0' will do conversion as you have requested but if there are any values that can not be converted the query will return an error like the following one:

ERROR: Could not convert "200 ... something" from column table_name.column_name to a number

Upvotes: 0

geoffrobinson
geoffrobinson

Reputation: 1580

I haven't done this before in Vertica, but my advice is the same for this type of problem. Try to figure out how PostgreSQL does it and try that since Vertica is utilizing a lot of PostgreSQL funcitonality.

I just did a quick search and came up with this as a possible solution: http://archives.postgresql.org/pgsql-general/2002-01/msg01057.php

A more thorough search may get you better answers.

Upvotes: 0

Related Questions