CODEWITHSUNDEEP
mysqlsqlmariadb
Iwan
Iwan

Reputation: 33

SQL get only earlier date in repetitive rows

I have an SQL table contains transactions like this

ID | FK | STATUS | DATE

1  | A  | K1     | 2021-01-01
2  | A  | K1     | 2021-01-15
3  | A  | K2     | 2021-01-30
4  | A  | K2     | 2021-02-03
5  | B  | K1     | 2021-01-12
6  | C  | K1     | 2021-03-30
7  | C  | K3     | 2021-09-15

As we can see, some FK have some records with different STATUS and different DATE. From each FK I want to get the first/earlier transaction date with status K1.

Maybe something like this.

ID | FK | STATUS | DATE
1  | A  |   K1   | 2021-01-01
5  | B  |   K1   | 2021-01-12
6  | C  |   K1   | 2021-03-30

How can I get the result like that?

Note : I'm using MariaDB version 10.5.9-MariaDB

Upvotes: 0

Views: 48

Answers (3)

ggordon
ggordon

Reputation: 10035

CREATE TABLE my_table (
  `ID` INTEGER,
  `FK` VARCHAR(1),
  `STATUS` VARCHAR(2),
  `DATE` DATETIME
);

INSERT INTO my_table
  (`ID`, `FK`, `STATUS`, `DATE`)
VALUES
  ('1', 'A', 'K1', '2021-01-01'),
  ('2', 'A', 'K1', '2021-01-15'),
  ('3', 'A', 'K2', '2021-01-30'),
  ('4', 'A', 'K2', '2021-02-03'),
  ('5', 'B', 'K1', '2021-01-12'),
  ('6', 'C', 'K1', '2021-03-30'),
  ('7', 'C', 'K3', '2021-09-15');

Query #1

SELECT 
    ID, 
    FK, 
    STATUS, 
    DATE 
FROM (
    SELECT 
        *, 
        row_number() over (partition by FK,STATUS order by DATE ASC) rn from my_table 
) t
where STATUS='K1' and rn=1;
ID FK STATUS DATE
1 A K1 2021-01-01 00:00:00
5 B K1 2021-01-12 00:00:00
6 C K1 2021-03-30 00:00:00

View on DB Fiddle

Upvotes: 2

Joel Coehoorn
Joel Coehoorn

Reputation: 415931

This is solved with a windowing function:

WITH RowNumbers AS (
    SELECT *, 
        row_number() OVER (PARTITION BY FK ORDER BY Date) As rn
    FROM [MyTable]
    WHERE Status = 'K1'
)
SELECT ID, FK, STATUS, DATE
FROM RowNumbers
WHERE rn = 1

Window functions were added to MariaDB in version 10.2. The CTE is also needed because row_number() isn't computed until after the WHERE clause is processed.

Upvotes: 0

MetaData
MetaData

Reputation: 116

WITH cte_customers AS (
    SELECT 
        ROW_NUMBER() OVER(PARTITION BY fk,status
             ORDER BY 
date
                
        ) row_num, 
        customer_id, 
        fk, 
        status
     FROM 
        sales.customers
) SELECT 
    *
FROM 
    cte_customers
WHERE 
    row_num =1;

Upvotes: 0

Related Questions