CODEWITHSUNDEEP
mysqlsql
silkfire
silkfire

Reputation: 25935

Select all rows later than a row matching a criterion

I've a difficult problem I can't seem to solve.

I need to select all rows with a date later than a row matching a certain criterion. In table #1, I've got a status flag. 1 indicates an error. So in this case, I want to select all rows with a ValueDate > 2016-02-02 (after row with ID 14), but only for those rows belonging to the same category.

Only one row can have an error flag per category.

enter image description here

It becomes a little bit more complicated. I've got another table, table #2, where the files are. These files are constantly added, so they may replace any erroneous files in table #1.

I want to select all rows after ID 14, only if there's a replacement file available for ID 14 (this is checked by verifying that the CreateDate of the replacement file is later than the erroneous file).

Can anyone guide me in the right direction?

Columns for table #1: id, file, status

Columns for table #2: id, category, CreateDate, ValueDate

Upvotes: 0

Views: 52

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

You can do this using a subquery in the where clause:

select t.*
from t
where t.date >= (select min(t2.date)
                 from t2
                 where t2.category = t.category and t2.status = 1
                );

You can do this as a join and an aggregation:

select t.*
from t join
     (select category, min(t2.date) as mindate
      from t2
      where t2.status = 1
     ) t2
     on t2.category = t.category and t.date > t2.mindate;

Upvotes: 1

Haider
Haider

Reputation: 11

I'm not sure if you request two or one query. I'll give you the two queries and tell me if that's not the answers you want.

The first one:

I want to select all rows with a ValueDate > 2016-02-02 (after row with ID 14), but only for those rows belonging to the same category.

SELECT * FROM TableALL A
WHERE A.ValueDate > (SELECT max(B.ValueDate) FROM TableALL B
                     WHERE B.category=A.category 
                       AND status=1)

the second one:

I want to select all rows after ID 14, only if there's a replacement file available for ID 14 (this is checked by verifying that the CreateDate of the replacement file is later than the erroneous file).

SELECT A.* FROM Table1 A, Table2 B
WHERE B.CreateDate > (SELECT max(Y.ValueDate) 
                      FROM Table1 Z,Table2 Y 
                      WHERE Z.id=Y.id 
                       AND Y.category = B.category  // I thought you  
                                                    // need it here too
                       AND status=1)
 AND A.id=B.id

Upvotes: 0

Related Questions