'How show mysql query without NULL?

How show mysql query without NULL? I want to show my quary where "LAG(title) OVER(PARTITION BY emp_no)" IS NOT NULL, I need only titles.emp_no = "LAG(title) OVER(PARTITION BY emp_no)"

My query:

SELECT titles.emp_no, LAG(title) OVER(PARTITION BY emp_no)

FROM titles;

enter image description here

mysqlsqlwindowsfunctionlag


Solution 1:[1]

Subquery your current query and filter based on the lag value:

WITH cte AS (
    SELECT emp_no, LAG(title) OVER (PARTITION BY emp_no ORDER BY <col>) lag_title
    FROM titles
)

SELECT emp_no, lag_title
FROM cte
WHERE lag_title IS NOT NULL;

Notice that I added an ORDER BY clause to LAG, without which is does not make much sense.

Solution 2:[2]

you could simply use:

select emp_no, max(title) as title
from titles
group by emp_no

Results would include some title:NULL if there is no row with that emp_no with title:NOT NULL. If you want to eliminate those, as well (and show only those where there is at least one nn-Null title:

select emp_no, max(title) as title
from titles
where title is not null
group by emp_no

Any specific reason for using LAG? it would make sense if you wanted to get a previous (ordered in a particular way) title (and that title was not-null); but you didn't specify an order.

Solution 3:[3]

@Vladyslav Nazarov If you want you can also use max(title)over(partition by emo_no order by ---only if you want or need----) in this case you increase performance and you don t use group by

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Tim Biegeleisen
Solution 2 tinazmu
Solution 3 Florin

Related Questions