CODEWITHSUNDEEP
sqldatabasesplitdistinctvertica
Kalpish Singhal
Kalpish Singhal

Reputation: 392

VERTICA Database :- how to get distinct count of "first names" where first name and last name are stored in single column?

I'm new to Vertica DB and was facing a problem. It is mostly like SQL but I have a Customer table 

Customer Table 
NAME         | AGE   | SEX
JOHN KENY    |26     |M
JOHN CENA    |32     |M
JOHN MCCAIN  |35     |M
PETER PAN    |33     |M
SELENA GOMEZ |24     |F

Now i would like an output of a query to run on vertica DB to Fetch me DISTINCT customer first name i.e

NAME
JOHN
PETER
SELENA

I'm Trying the SPLIT_PART() function in Vertica but I am not able to execute the query correctly 

SELECT DISTINCT NAME FROM
  (SELECT SPLIT_PART(NAME,' ',1) from Customer );

gives

ERROR SYNTAX error at or near "Select"

I also tried 

SELECT SPLIT_PART(SELECT DISTINCT NAME FROM Customer,' ',1);

resulting in

ERROR SYNTAX error at or near "Select"

but 

SELECT SPLIT_PART('JOHN KENY',' ',1) ;

outputs

JOHN

Upvotes: 0

Views: 514

Answers (1)

Nicolas Riousset
Nicolas Riousset

Reputation: 3619

The following query should do the job :

select distinct SPLIT_PART(NAME,' ',1) from Customer

However, note that this is fragile. If this is a production environment (and not a simple exercise), I bet you'll end up with names containing spaces that will break your query.

Upvotes: 1

Related Questions