I am trying to retrieve rows with same Volume value or with only 1 Volume, but could not come up with a SQL logic.

Data:

Required Output:

CodePudding user response：

This one is achievable using a subquery.

select * from test where col1 in (
  select t.col1
  from(
    select col1, col2,
        dense_rank() over (partition by col1 order by col2) as dr
    from test) t
  group by  t.col1
  having sum(case when t.dr = 1 then 0 else t.dr end) = 0)

Try this dbfiddle.

CodePudding user response：

This can be done on a more easy way:

select t1.id,
       t1.volume
from tbl t1
inner join (select id
            from tbl
            group by id
            having count(distinct volume) = 1
            ) as t2 on t1.id=t2.id;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=92bc234e631a1106b0e322bc4954d696

having count(distinct volume) = 1 will return only the id that have the same volume , including the id with just one volume.

CodePudding user response：

I'd naturally be inclined towards Ergest Basha's pattern.

It can also be expressed using NOT EXISTS()

SELECT
  t.*
FROM
  tbl AS t
WHERE
  NOT EXISTS (
    SELECT *
      FROM tbl
     WHERE id = t.id
       AND volume <> t.volume
  )

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=243c42008f527391514d1ad124730587