select the rows based on unique column of each group from the table

Question

----------------
c_id  s_id   p_id    (customer_id, service_id, provider_id)
---- ---- ------  
1,     1,     1 - not elegible to select as next entry has same p_id
1,     2,     1
1,     3,     3

2,     1,     1
2,     2,     2
2,     3,     3

3,     1,     3 - not elegible to select as next entry has same p_id
3,     2,     3 - not elegible to select as next entry has same p_id
3,     3,     3

What is the cost effective way of writing the query to produce below result from above data ?

 ----------------
 c_id  s_id  p_id 
 ---- ---- ------ 
  1,    2,   1
  1,    3,   3

  2,    1,   1
  2,    2,   2
  2,    3,   3

  3,    3,   3

*not elegible to select as next entry has same p_id* The term "next" makes sense only after specifying the sorting order. Does ORDER BY s_id within the same c_id is safe ordering? Does s_id contains natural numbers from 1 and without skipped values for each separate c_id? Does it is possible that some c_id have p_id values like 1,2,1,1? — Akina, Sep 10 '20 at 19:14
@Akina I can add new column `timestamp` to each row to sort p_id based on timestamp and select latest common p_id. — Nagendra Shanbhag, Sep 10 '20 at 19:26

GMB · Answer 1 · 2020-09-10T19:39:10.003

2

In MySQL 8.0, you can use lead() to retrieve the "next" p_id, and use that information to filter out rows whose pid is the same as the next value.

select *
from (
    select t.*, lead(p_id) over(partition by c_id order by s_id) lead_p_id
    from mytable t
) t
where not p_id <=> lead_p_id

In earlier versions, you would typically use a correlated subquery:

select t.*
from mytable t
where not pid <=> (
    select p_id
    from mytable t1
    where t1.c_id = t.c_id and t1.s_id > t.s_id
    order by t1.s_id
    limit 1
)

edited Sep 10 '20 at 19:39

answered Sep 10 '20 at 19:12

GMB

216,147
25
84
135

@Akina: Thanks. I added a solution for earlier versions. – GMB Sep 10 '20 at 19:18
Assume `p_id` for some `c_id` is 1,2,1,1... 1st row will be lost. – Akina Sep 10 '20 at 19:19
@Akina: I don't think so (if I understand correctly what you mean). Both queries consistently use `s_id` to sort rows having the same `p_id`. – GMB Sep 10 '20 at 19:40
yes he's right @Akina . Have a look at [this](https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=1db6b2b80dbf3ee8c443c0653fe997bc) . Congrats GMB, such a wonderful solution. – Barbaros Özhan Sep 10 '20 at 19:56

score 0 · Answer 2 · answered Sep 10 '20 at 19:11

0

I'm not too sure if this is the most cost effective, but it def seems to be the most obvious solution I could think of.

select 
   c_id
   ,max(s_id) [s_id]
   ,p_id
from
  `table_name`
group by
   c_id
   ,p_id

answered Sep 10 '20 at 19:11

Spencer Tibbitts

96
3

score 0 · Answer 3 · answered Sep 10 '20 at 20:13

0

If s_id is a sequence with no gaps, you can use:

select t.*
from t left join
     t tnext
     on tnext.c_id = t.c_id and tnext.s_id = t.sid + 1
where not (tnext.p_id <=> t.p_id);

answered Sep 10 '20 at 20:13

Gordon Linoff

1,242,037
58
646
786

select the rows based on unique column of each group from the table

3 Answers3