How to create SQL code to return value according to data specifications-CodePudding

I have a table in mysql with the structure below

id        int pk
client    varchar
dateOne   date
descOne   float
material  varchar
dateTwo   date
descTwo   float

Here some examples of my data in the table

client        dateOne descOne material  dateTwo   descTwo
1000009999  30.09.2020  60  Null        0
1000009999  18.08.2020  70  Null        0
1000009999  06.07.2018  50  Null        0
1000009999  30.09.2020  60  1000004 30.09.2020  60
1000009999  30.09.2020  60  1000004 17.11.2020  55
1000009999  30.09.2020  60  1000004 21.03.2022  60
1000009999  30.09.2020  60  1000004 19.04.2022  60
1000009999  30.09.2020  60  1000002 30.09.2020  60
1000009999  30.09.2020  60  1000002 17.11.2020  55
1000009999  30.09.2020  60  1000002 21.03.2022  60
1000009999  30.09.2020  60  1000002 19.04.2022  60
1000009999  30.09.2020  60  1000001 30.09.2020  60
1000009999  30.09.2020  60  1000001 17.11.2020  55
1000001163  30.09.2020  60  Null        0
1000001163  18.08.2020  70  Null        0
1000001163  06.07.2018  50  Null        0
1000001163  30.09.2020  60  1000004 30.09.2020  60
1000001163  30.09.2020  60  1000004 17.11.2020  55
1000001163  30.09.2020  60  1000004 21.03.2022  60
1000001163  30.09.2020  60  1000004 19.04.2022  60
1000001163  30.09.2020  60  1000002 30.09.2020  60
1000001163  30.09.2020  60  1000002 17.11.2020  55
1000001163  30.09.2020  60  1000002 21.03.2022  60
1000001163  30.09.2020  60  1000002 19.04.2022  60
1000001163  30.09.2020  60  1000001 30.09.2020  60
1000001163  30.09.2020  60  1000001 17.11.2020  55

I need to build a SQL query that does the following check:

Return by customer and material;

If the 'material' field is filled in, it returns the most recent 'descTwo' according to 'dateTwo'

If the 'material' field is 'null', return the most recent 'descOne' according to 'descOne'

Below is an example of the expected result:

client        dateOne descOne material  dateTwo   descTwo
1000009999  30.09.2020  60  Null        0
1000009999  30.09.2020  60  1000004 19.04.2022  60
1000009999  30.09.2020  60  1000002 19.04.2022  60
1000009999  30.09.2020  60  1000001 17.11.2020  55
1000001163  30.09.2020  60  Null        0
1000001163  30.09.2020  60  1000004 19.04.2022  60
1000001163  30.09.2020  60  1000002 19.04.2022  60
1000001163  30.09.2020  60  1000001 17.11.2020  55

CodePudding user response：

Please Please Please use ISO-8601 date notation, such as YYYY-MM-DD, it makes answering questions much easier...

As you've supplied invalid dates, I've written a demo using strings and STR_TO_DATE().

WITH
  prioritised AS
(
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY client, material
          ORDER BY STR_TO_DATE(dateTwo, '%d.%m.%Y') DESC,
                   STR_TO_DATE(dateOne, '%d.%m.%Y') DESC
    )
      AS priority
  FROM
    example
)
SELECT
  *
FROM
  prioritised
WHERE
  priority = 1

Demo : https://dbfiddle.uk/JyVlxebZ