Question 237639 in Mysql Question posted in
The official MySQL documentation can be found here.

How can I implement IGNORE NULLS in window functions such as FIRST_VALUE and LAST_VALUE in MySQL?

How can I implement IGNORE NULLS in window functions such as FIRST_VALUE and LAST_VALUE in MySQL?

For example, let’s say we have a very simple table called sales.

SaleID CustomerID SaleRegion
1 7 CITY A
2 10 NULL
3 10 NULL
4 7 CITY A
5 10 CITY B
6 10 CITY C

How can I get the first value of sale region partitioned by customer id?

Expected (Desired) Output:

CUSTOMERID FIRST_VALUE(SALEREGION)
7 CITY A
7 CITY A
10 CITY B
10 CITY B
10 CITY B
Tags:

2

Answers


  1. 0

    You need to ORDER BY SaleRegion DESC in the window function.

    If one customer has multiple regions, you need to refine the Order by

    CREATE TABLE customers
    (`aleID` int, `CustomerID` int, `SaleRegion` varchar(6))
;
    
INSERT INTO customers
    (`aleID`, `CustomerID`, `SaleRegion`)
VALUES
    (1, 7, 'CITY A'),
    (2, 10, NULL),
    (3, 10, NULL),
    (4, 7, 'CITY A'),
    (5, 10, 'CITY B')
;


    SELECT
`CustomerID`, FIRST_VALUE(`SaleRegion`) OVER( PARTITION BY `CustomerID` ORDER BY `SaleRegion` DESC
  )  as SaleRegion
  FROM customers
    CustomerID SaleRegion
    7 CITY A
    7 CITY A
    10 CITY B
    10 CITY B
    10 CITY B

    fiddle

    Login or Signup to reply.
  2. 0

    If you want to ignore nulls completely, you can always use an inner query to remove nulls before passing the values to window function or any expression.

    In the given example;

    SELECT CustomerID
    ,FIRST_VALUE(SaleRegion) OVER(PARTITION BY CustomerID) as SaleRegion
FROM (SELECT * FROM table_name WHERE SaleRegion IS NOT NULL) as new_table

    Hope this helps.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search