MYSQL - get first record based on column value

Question

I have the following employee_sequence table

| id | employee_id | sequence_id | is_completed |
|----|:-----------:|:-----------:|:------------:|
| 1  |      12     |      3      |       1      |
| 2  |      12     |      4      |       1      |
| 3  |      10     |      3      |       1      |
| 4  |      10     |      4      |       0      |

I am looking for how to get, in 1 query, the first row having is_completed = 0 for an employee. If no such row for that employee, then take first row having is_completed = 1

Example for employee_id = 12 (there is no is_completed = 0, so first row having is_completed = 1)

| id | employee_id | sequence_id | is_completed |
|----|:-----------:|:-----------:|:------------:|
| 1  |      12     |      3      |       1      |

Example for employee_id = 10 (first row having is_completed = 0)

| id | employee_id | sequence_id | is_completed |
|----|:-----------:|:-----------:|:------------:|
| 4  |      10     |      4      |       0      |

GMB · Answer 1 · 2020-10-10T12:30:53.873

1

You can use row_number(), if yu are running MySQL 8.0; assumuming that id can be used to order the records, you would phrase this as:

select *
from (
    select es.*, row_number() over(partition by employee_id order by is_completed, id) rn
    from employee_sequence es
) es
where rn = 1

In ealier version, an alternative is a correlated subquery with a row-limiting clause:

select *
from employee_sequence es
where es.id = (
    select es1.id
    from employee_sequence es1
    where es1.employee_id = es.employee_id
    order by es1.is_completed, es.id
    limit 1
)

edited Oct 10 '20 at 12:30

answered Oct 10 '20 at 10:42

GMB

216,147
25
84
135

A very valid point. Your answer prefers `is_completed = 1` over `is_completed = 0`. In other words, your `order by` clause is incorrect. Re-read the question. – Booboo Oct 10 '20 at 12:26

Gordon Linoff · Answer 2 · 2020-10-10T17:14:07.473

1

If you want this per employee (as your question suggests):

select es.*
from employee_sequence es
where es.employee_id = 12
order by is_completed, id
limit 1;

Here is a db<>fiddle.

edited Oct 10 '20 at 17:14

answered Oct 10 '20 at 11:28

Gordon Linoff

1,242,037
58
646
786

This query doesn't give any results – user1841557 Oct 10 '20 at 13:46
@user1841557 . . . The only way this query could not give results is if there is no row with `employee_id = 12`. – Gordon Linoff Oct 10 '20 at 14:20
@user1841557 . . . That comment i even more baffling. Why are you saying this won't work? – Gordon Linoff Oct 10 '20 at 15:28
I tried the query you suggested. It does not solve the problem, returns no result. The post by GMB, below works best for me. – user1841557 Oct 10 '20 at 16:47
@user1841557 . . . I added a db<>fiddle. This code works and it does exactly what you specify. – Gordon Linoff Oct 10 '20 at 17:14
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/222851/discussion-between-user1841557-and-gordon-linoff). – user1841557 Oct 11 '20 at 06:58

score 0 · Answer 3 · answered Oct 10 '20 at 11:17

If you are running something less than MySql 8 then:

select * from employee_sequence e1
where e1.id = (
  select e2.id
  from employee_sequence e2
  where e2.employee_id = e1.employee_id
  order by e2.is_completed, e2.sequence_id
  limit 1
);


| id  | employee_id | sequence_id | is_completed |
| --- | ----------- | ----------- | ------------ |
| 1   | 12          | 3           | 1            |
| 4   | 10          | 4           | 0            |

View on DB Fiddle

MYSQL - get first record based on column value

3 Answers3