select rows with condition in other rows

Question

I want select rows from my table with last status_Id if there is a row with status_Id = 2 for that rows

ticketStatus_Id  ticket_Id   status_Id
======================================
1                 1            1
2                 1            2 -
3                 1            3 *
4                 2            1
5                 3            1
6                 3            2 - *
7                 4            1
8                 4            2 -
9                 4            3      
10                4            4 *

I want select just rows 3, 6, 10. there are another rows with status_Id = 2 (rows 2, 6, 8) for that ticket_Id, In other word How to select rows 3,6,10 with ticket_Id =1,3,4 that there are another row with these ticket_Ids and status_Id=2 (rows 2,6,8)

Answer 1

First, for each ticket we get the row with the highest status. We can do this with a self-join. Each row is joined with the row with the next highest status. We select the rows which have no higher status, those will be the highest. Here's a more detailed explanation.

  select ts1.*
  from ticket_statuses ts1
  left outer join ticket_statuses ts2
    on  ts1.ticket_Id = ts2.ticket_Id
    and ts1.status_Id < ts2.status_Id
  where ts2.ticketStatus_Id is null

3   1   3
4   2   1
6   3   2
10  4   4
11  5   3

Note that I've added a curve-ball of 11, 5, 3 to ensure we only select tickets with a status of 2, not greater than 2.

Then we can use that as a CTE (or subquery if you're not using MySQL 8) and select only those tickets who have a status of 2.

with max_statuses as (
  select ts1.*
  from ticket_statuses ts1
  left outer join ticket_statuses ts2
    on  ts1.ticket_Id = ts2.ticket_Id
    and ts1.status_Id < ts2.status_Id
  where ts2.ticketStatus_Id is null
)
select ms.*
from max_statuses ms
join ticket_statuses ts
  on  ms.ticket_id = ts.ticket_id
  and ts.status_id = 2;

3   1   3
6   3   2
10  4   4

This approach ensures we select the complete rows with the highest statuses and any extra data they may contain.

dbfiddle

Answer 2

If you want the complete row, then I would view this as exists:

select t.*
from t
where exists (select 1
              from t t2
              where t2.ticket_id = t.ticket_id and t2.status_id = 2
             ) and
      t.status_Id = (select max(t2.status_id)
                      from t t2
                      where t2.ticket_id = t.ticket_id 
                     );

If you just want the ticket_id and status_id (and not the whole row), I would recommend aggregation:

select ticket_id, max(status_id)
from t
group by ticket_id
having sum(status_id = 2) > 0;

In your case, ticketStatus_Id seems to increase with status_id, so you can use:

select max(ticketStatus_Id) as ticketStatus_Id, ticket_id, max(status_id) as Status_Id
from t
group by ticket_id
having sum(status_id = 2) > 0;

Answer 3

This is basicaly a "last row per group" problem. You will find some solutions here. My prefered solution would be:

select t.*
from (
  select max(ticketStatus_Id) as ticketStatus_Id
  from mytable
  group by ticket_Id
) tmax
join mytable t using(ticketStatus_Id)

The difference in your question is that you have a condition requiring a specific value within the group. This can be solved with a JOIN within the subquery:

select t.*
from (
  select max(t1.ticketStatus_Id) as ticketStatus_Id
  from mytable t2
  join mytable t1 using(ticket_Id)
  where t2.status_Id = 2
  group by t2.ticket_Id
) tmax
join mytable t using(ticketStatus_Id)

Result:

| ticketStatus_Id | ticket_Id | status_Id |
| --------------- | --------- | --------- |
| 3               | 1         | 3         |
| 6               | 3         | 2         |
| 10              | 4         | 4         |

View on DB Fiddle

A solution using window functions could be:

select ticketStatus_Id, ticket_Id, status_Id
from (
  select *
  , row_number() over (partition by ticket_Id order by ticketStatus_Id desc) as rn
  , bit_or(status_Id = 2) over (partition by ticket_Id) > 0 as has_status2
  from mytable
) x
where has_status2 and rn = 1

A quite expressive way is to use EXISTS and NOT EXISTS subquery conditions:

select t.*
from mytable t
where exists (
    select *
    from mytable t1
    where t1.ticket_Id = t.ticket_Id
      and t1.status_Id = 2
  )
  and not exists (
    select *
    from mytable t1
    where t1.ticket_Id = t.ticket_Id
      and t1.ticketStatus_Id > t.ticketStatus_Id
  )

Answer 4

SELECT a.*
FROM t a 
JOIN 
(
  SELECT ticket_id, MAX(status_id) max_status_id 
  FROM t 
  WHERE status_id >= 2 
  GROUP BY ticket_id 
) b 
ON a.ticket_id = b.ticket_id 
  AND a.status_id = b.max_status_id;

Answer 5

SELECT
  MAX(m1.ticketstatus_Id) as ticket_status,
  m1.ticket_Id             as ticket,
  MAX(m1.status_Id)        as status
FROM mytable m1
WHERE
  m1.ticket_Id in (select m2.ticket_Id from mytable m2 where m2.ticket_Id=m1.ticket_Id and m2.status_Id=2)
GROUP BY m1.ticket_Id