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

CodePudding user response：

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.

CodePudding user response：

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.