Please tell me there is such a table: enter image description here

Depending on the osn variable, you need to select rows grouped by vidv and maximum date:

  1. If osn = 3030, then select lines 12 and 13.
  2. If osn = 60090, then select lines 12 and 13.
  3. And if osn = 20370, then choose nothing, since there is a line with razdel = '3030', vidv = '1' and a later date.

Here is the code written, then stuck:

SELECT * FROM ( SELECT id, max(sroks), razdel, vidv FROM ( SELECT id, sroks, srokpo, replace (razdel,' ', '') as razdel, vidv FROM vpl.manrazdel ) GROUP BY id, razdel, vidv ) where id='037016034158607001409576221452' and "Код" IN (@osn)

that's what it gives out:

enter image description here

I will try to translate into human language. You need to select the current razdel and vidv. Relevance is determined by date. Ie if there are several identical vidv, but with different razdel value, then the current vidv will be with a later date. For example, vidv = 1, razdel = 20370, sroks = 04/01/2016 and vidv = 1, razdel = 3030, sroks = 04/01/2017, the second example will be relevant here, since it has a later date. I solved this half of the problem with the above code.

But there is one more condition. If the value of the variable is equal to the current razdel, select ALL of the actual data and hide the non-relevant. If osn = 3030, then select lines 12 and 13. If osn = 60090, then select lines 12 and 13.

If the value of the variable is not the current razdel, do not select anything. If osn = 20370, then select nothing, since there is a line with the current razdel = '3030', vidv = '1' and later date than razdel = '20370', in this case razdel = '20370' is not relevant.

  • As I do not understand the logic of the sample. - Raz Galstyan
  • understood nothing about osn and vidv. - Denis
  • Why do not you specify in the question what kind of DBMS do you have, the possibilities are different for everyone and the approaches to solving the problem are the same ... Having rummaged 10 minutes in your previous questions, I certainly managed to guess that this is DB2 ... But always specify in the text question which DBMS is used. And now I still have to spend 10 minutes to interrupt your data from images to test the request ... Always attach the input data as text! - Mike
  • The fact that you want to first get the string for the maximum dates within the vidv I understood (done through row_number ()). But this is why you want to get the same result with osn = 3030 and at 60090 I did not understand at all. - Mike
  • I will try to translate into human language. You need to select the current razdel and vidv. Relevance is determined by date. Ie if there are several identical vidv, but with different razdel value, then the current vidv will be with a later date. For example, vidv = 1, razdel = 20370, sroks = 04/01/2016 and vidv = 1, razdel = 3030, sroks = 04/01/2017, the second example will be relevant here, since it has a later date. I solved this half of the problem with the above code. - J. Doe

1 answer 1

DB2 is not at hand, check where. I hope your version supports window functions ... If something is different in the supported syntax, debug it in parts, starting with an internal subquery.

 select * from ( select razdel, vidv, sroks, max(case when razdel=3030 then 1 end) over() test from ( select razdel, vidv, sroks, row_number() over(partition by vidv order by sroks desc) RN from vpl.manrazdel where id='037016034158607001409576221452' ) as X where RN=1 ) as Y where test=1

In the internal subquery, we number the lines in the order of decreasing dates, starting with numbering from 1 for each vidv. We take only the first lines (with the maximum date in the group). At the next query level, we get the maximum from the formula that returns 1 if the condition on razdel coincided. Since this maximum is taken in a window function with an empty over() we will get 1 on all sample records in the case that at least one row has the same condition. It remains at the outermost level of the request to make sure that 1 has been installed.

  • Thank you very much! - J. Doe