Solved

SQL Query Help

  • 6 January 2021
  • 4 replies
  • 539 views

Userlevel 4
Badge +10

Can someone help me rectify this query? Using Oracle 12C

Trying to Pick Latest Activity date for each Customer Per state to use as subquery to gather rest of the data

Here is example data

Customer_no

State

Activity_date

110913

CT

8/2/2019

110913

MA

9/22/2020

110913

MA

11/23/2020

110913

MA

11/23/2020

 

Desired result

 

Customer_no

State

Activity_date

110913

CT

8/2/2019

110913

MA

11/23/2020

 

 

The query I write  is following: looks like max() can’t pick one from same activity_date

Distinct is also not working…

 

select customer_no,tax_State,max(activity_date)

 from table   

where customer_no ='110913'

group by customer_no,tax_State

 

and it bring this data

 

Customer_no

State

Activity_date

110913

CT

8/2/2019

110913

MA

9/22/2020

icon

Best answer by durette 6 January 2021, 21:56

View original

This topic has been closed for comments

4 replies

Userlevel 7
Badge +18

Your result looks like ACTIVITY_DATE is stored as a string and is not truly a date.

If that's the case, you should be able to convert it from a string to a date for the evaluation.

   SELECT customer_no,
tax_state,
MAX(TO_DATE(activity_date, 'MM/DD/YYYY')) AS last_activity_date
FROM table
WHERE customer_no = '110913'
GROUP BY customer_no, tax_state

 

Userlevel 4
Badge +10

@durette that’s true, the issue was with data type, 

Thanks for replying

Userlevel 4
Badge +10

@durette oops, i wanted to mark your answer as Best Answer and accidently clicked on my own…. now i can’t change it………. bummer

Userlevel 7
Badge +18

@durette oops, i wanted to mark your answer as Best Answer and accidently clicked on my own…. now i can’t change it………. bummer

Done!