Clever Geek Handbook

SQL Select only rows with maximum value in FILTERED by Column

This is an answer to a great answer:
SQL Select only rows with maximum value in a column

SQLFiddle http://sqlfiddle.com/#!2/3077f/2

The "yourtable" table: 

| id | val | ignore | content |
-------------------------------
| 1  | 10  |   0    |   A     |
| 1  | 20  |   0    |   B     |
| 1  | 30  |   1    |   C     |
| 2  | 40  |   0    |   D     |
| 2  | 50  |   0    |   E     |
| 2  | 60  |   1    |   F     |

When searching for max val for id, the following sql is used:

select yt1.*
from yourtable yt1
left outer join yourtable yt2
on (yt1.id = yt2.id and yt1.val < yt2.val )
where yt2.id is null;

So the result will be in this case

| id | val | ignore | content |
-------------------------------
| 1  | 30  |   1    |   C     |
| 2  | 60  |   1    |   F     |

The question is how to filter on the "ignore" column when it is 1, so the result will be

| id | val | ignore | content |
-------------------------------
| 1  | 20  |   0    |   B     |
| 2  | 50  |   0    |   E     |
+2


source to share


2 answers


You need to put a condition in both the subquery and the outer query:



select yt1.*
from yourtable yt1 left outer join
     yourtable yt2
     on yt1.id = yt2.id and yt1.val < yt2.val and yt2.ignore <> 1
where yt2.id is null and yt1.ignore <> 1;
+3


source


You can simply add another condition and yt1.ignore <> 1

like below:



select yt1.*
from yourtable yt1
left outer join yourtable yt2
on (yt1.id = yt2.id and yt1.val < yt2.val )
where yt2.id is null and yt1.ignore <> 1;
+1


source






More articles:


All Articles