How to pull unique values from a table?

Database
Enthusiast

How to pull unique values from a table?

Hi, so I am trying to pull distinct valus from my table 1 based on a criteria. I am attaching my table below called Table1. Sorry I don't know how to paste a better version.

 

I like to pull unique csutomer Id based on the note. I want to pull unique customer if the notes has the word CAR  in it. so for my customer XY1 he has two notes where the word CAR is available but needs to pull it once. My customer AZ1 has the word CAR repeated twice but again I want to pull one row indicating that my customer has the word CAR in his notes. I have millions of rows in my table and only I only care about pulling one row of data for all customers with a word CAR in their notes no matter what the date is.

 

I don't know if this should use the function MAX or maybe do a subquery but I am struggling.

 

I tried this but didn't work:

 

select DISTICNT Customer ID from(Select  Customer ID, date, note from Table 1

where Upper (Note) like %CAR%)

 

Any help is appreciated.

Mike

Customer IDdatenote
XY112/1/2018THE CUSTOMER BOUGHT A CAR
XY11/5/2018CAR IS IN SERVICE
XY12/5/2017CUSTOMER BOUGHT A TRUCK
AZ112/1/2018CAR IS IN MAINTENANCE
AZ112/1/2018CAR IS READY
AZ11/1/2017TRUCK IS READY
AZ11/9/2017BOAT IS IN SERVICE

Accepted Solutions
Teradata Employee

Re: How to pull unique values from a table?

Apart from some typos, if you use a subquery (also known as a derived table expression) then you must provide an alias / correlation name in the FROM clause:

select DISTINCT Customer_ID
 from (Select  Customer_ID, "date", note from Table_1
where Upper (Note) like '%CAR%') AS SQ1;

But you don't need the subquery in this case.

 

select DISTINCT Customer_ID
 from Table_1
where Upper (Note) like '%CAR%'
1 ACCEPTED SOLUTION
4 REPLIES 4
Teradata Employee

Re: How to pull unique values from a table?

Apart from some typos, if you use a subquery (also known as a derived table expression) then you must provide an alias / correlation name in the FROM clause:

select DISTINCT Customer_ID
 from (Select  Customer_ID, "date", note from Table_1
where Upper (Note) like '%CAR%') AS SQ1;

But you don't need the subquery in this case.

 

select DISTINCT Customer_ID
 from Table_1
where Upper (Note) like '%CAR%'
Enthusiast

Re: How to pull unique values from a table?

Thanks Fred! I tried the query you wrote before but the results still showing all rows where CAR is mentionned. It's not pulling a distinct row for each customer based on the Note and since the notes are different but CAR still exist in most of them, the query is pulling every single row where the word CAR is present. do you think a max function is required?

Highlighted
Junior Contributor

Re: How to pull unique values from a table?

If you only want the customer id Fred's query is fine, but if you want to return additional columns you need a ROW_NUMBER:

Select *
from Table 1
where Upper (Note) like %CAR%
QUALIFY
   ROW_NUMBER() OVER (PARTITION BY Customer ID ORDER BY date DESC) = 1

Btw, there's no need for UPPER if you run in a Teradata mode session, because LIKE is case insensitive by default.

 

Enthusiast

Re: How to pull unique values from a table?

Thanks for the clarification Dnoeth. both queries work fine! I have to pick Fred as an accepted solution because he was first to respond