Question: 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.

Answer: 
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](http://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=f2b476c01046d253cacbd383a2852ae7)**

---

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;

```
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;

```