CODEWITHSUNDEEP
sqloracle-database
Ashley Clarke
Ashley Clarke

Reputation: 1

ORACLE: SQL; Select a distinct records if and only if one column changed

Thanks in advance for your help. I need to create a query to select distinct columns if and only if another column changed. However, each record has a date_code which makes every record unique. I would like to pull the last record before the change as well as the last record after the change.

Here is an example: A customer record with an account number, company name with a date code stamp. I want to get the last record before a company name change. Even if a customer goes back to the company, the last record for a customer for a specific company would be the last date code.

Table Example: Example of table data

Expected Result: Example of expected Results

Upvotes: 0

Views: 225

Answers (3)

Popeye
Popeye

Reputation: 35900

You can use simple lead function as mentioned in one of the answers with simple case..when as follows:

select * from
(select t.*,
       case
         when lead(company_name) over(partition by customer_account order by datecode) = company_name 
         then 0
         else 1
       end as result
  from cust_account t)
where result = 1

Upvotes: 0

astentx
astentx

Reputation: 6751

You can use analytic functions LAG/LEAD to calculate next/previous row's value and compare it with current value. This will also work in many other databases.

Fiddle here.

with q as (
  select a.*,
    lead(company_name, 1, '__')
      over(partition by customer_account order by datecode asc) as next_comp
  from cust_account a
)
select
  datecode,
  customer_account,
  company_name
from q
where company_name != next_comp
order by datecode asc

+---------+------------------+--------------+
|DATECODE | CUSTOMER_ACCOUNT | COMPANY_NAME |
+---------+------------------+--------------+
|20201104 | 10001000000004   | Apple        |
|20201106 | 10001000000004   | Microsoft    |
|20201108 | 10001000000004   | Google       |
|20201110 | 10001000000004   | Apple        |
+---------+------------------+--------------+

Upvotes: 1

MT0
MT0

Reputation: 167774

You can use MATCH_RECOGNIZE:

SELECT datecode,
       customer_account,
       company_name
FROM   table_name
MATCH_RECOGNIZE (
  PARTITION BY customer_account
  ORDER     BY datecode
  MEASURES
    LAST( datecode ) AS datecode,
    LAST( company_name ) AS company_name
  ONE ROW PER MATCH
  PATTERN ( company* same_company  )
  DEFINE
    company AS FIRST( company_name ) = NEXT( company_name )
)

Which, for the sample data:

CREATE TABLE table_name ( datecode, customer_account, company_name ) AS
SELECT DATE '2020-11-01', 10001000000004, 'Apple' FROM DUAL UNION ALL
SELECT DATE '2020-11-02', 10001000000004, 'Apple' FROM DUAL UNION ALL
SELECT DATE '2020-11-03', 10001000000004, 'Apple' FROM DUAL UNION ALL
SELECT DATE '2020-11-04', 10001000000004, 'Apple' FROM DUAL UNION ALL
SELECT DATE '2020-11-05', 10001000000004, 'Microsoft' FROM DUAL UNION ALL
SELECT DATE '2020-11-06', 10001000000004, 'Microsoft' FROM DUAL UNION ALL
SELECT DATE '2020-11-07', 10001000000004, 'Google' FROM DUAL UNION ALL
SELECT DATE '2020-11-08', 10001000000004, 'Google' FROM DUAL UNION ALL
SELECT DATE '2020-11-09', 10001000000004, 'Apple' FROM DUAL UNION ALL
SELECT DATE '2020-11-10', 10001000000004, 'Apple' FROM DUAL;

Outputs:

DATECODE            | CUSTOMER_ACCOUNT | COMPANY_NAME
:------------------ | ---------------: | :-----------
2020-11-04 00:00:00 |   10001000000004 | Apple       
2020-11-06 00:00:00 |   10001000000004 | Microsoft   
2020-11-08 00:00:00 |   10001000000004 | Google      
2020-11-10 00:00:00 |   10001000000004 | Apple

db<>fiddle here

Upvotes: 1

Related Questions