Select query by filtering multiple rows

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.

Lukasz Szozda · Accepted Answer · 2017-08-17 12:44:08Z

2

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;

edited Aug 17, 2017 at 12:44

answered Aug 17, 2017 at 12:35

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sabuj Hassan Over a year ago

huh, you must be kidding!!! Thanks. Will accept as answer once I am allowed to accept.

Caius Jard Over a year ago

Wouldn't HAVING MIN(status) > 0 have been more self explanatory?

Lukasz Szozda Over a year ago

@CaiusJard Yes, I thought about it as well but I don't have info about different status values (like -1,-2). SUM() is quite general and safer in this case.

Shreyas SG · Accepted Answer · 2017-08-20 04:06:41Z

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;

edited Aug 20, 2017 at 4:06

answered Aug 17, 2017 at 12:44

Shreyas SG

3883 gold badges6 silver badges21 bronze badges

2 Comments

Caius Jard Over a year ago

i understand where you're going with this but it's unlikely to be what the OP wants for a few reasons; it makes no attempt to exclude items that have a status of 0 in any row, and it might also return more than one row for an item if the status is highest twice with equal value

Shreyas SG Over a year ago

@CaiusJard, Updated my query. Hope this addresses your comments :)

Collectives™ on Stack Overflow

Select query by filtering multiple rows

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related