CODEWITHSUNDEEP
oracle-database
Sabuj Hassan
Sabuj Hassan

Reputation: 39443

Select query by filtering multiple rows

I have a table like this:

col | status
----+-------
1   |  0
1   |  1
2   |  1
2   |  2
3   |  1
3   |  0

I want to select only the row with having max status value. But also want to ignore if there is any 0 in status. So expected rows would be as follows (I am ignoring 1 and 3 for having status = 0).

col | status
----+-------
2   |  2

I only can pick the rows based on max(). But when I add another clause to filter the zero, it doesn't work.

SELECT col, max(status)
  FROM my_table
 WHERE
    (select count(*) 
    from my_table t1
    where t1.col = col
    and status = 0) = 0 
 GROUP BY col;

Any guide will do for me.

Upvotes: 2

Views: 53

Answers (2)

Shreyas SG
Shreyas SG

Reputation: 388

I'm a novice at SQL, but I'm sure rank() will serve the purpose.

select tabl.col, tabl.status from
(select col, status, rank over(status desc) as rnk
from tab where status = 0) tabl
where tabl.rnk = 1
and rownum = 1;

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 176189

Use HAVING clause:

SELECT col, MAX(STATUS)
FROM tab
GROUP BY col
HAVING SUM(CASE WHEN STATUS = 0 THEN 1 ELSE 0 END) = 0;

DBFiddle

If your minimal value for STATUS is 0 then you could use:

SELECT col, MAX(STATUS)
FROM tab
GROUP BY col
HAVING MIN(STATUS) > 0;

Upvotes: 2

Related Questions