There is a table tbl. somewhere on the other side, a service is launched, which with a certain time interval adds a new entry to the table.

example:

3 fields varA varchar, intB integer, timeC time

5 records

'value', 1, 19-00
'value', 2, 19-01
'value', 2, 19-02
'value', 3, 19-03
'value', 2, 19-04

you need to get only those records in which the value of the second field has changed, including the value that was ever before.

In this example, we get:

First recording

in the second record, the value has changed from 1 to 2, it should get into the resultset

in the third record, the value has not changed, it should NOT fall into the Resultset

in the fourth record changed from 2 to 3, in RESALTset

in the fifth one, it changed from 3 back to 2 (2 has already been done before), according to the conditions of the problem this record MUST get into resultset

Total we get that the resultset should consist of 4 entries

'value', 1, 19-00
'value', 2, 19-01
'value', 3, 19-03
'value', 2, 19-04

I can think up only to pass it the cursor, but it would be desirable selekt. I don't like cursors, I find them slow

  • This is for ruSO users specifically a problem, so as not to be idle? - Alexey Shimansky
  • What is the sampling logic? Why should be exactly 4? Source records are always 5? And if there will be 100? - AkaInq
  • Yes, the logic is clear, a certain service writes to the database one record every minute, it is necessary to issue not all records without exception, but only those where something (second field) has changed. Including changed to the value that was once before. 5 records for example, well, maybe 100 - user211576
  • Comment on each line of the result set with the reason why it got into it. - D-side
  • completely rewritten, I think, so the logic is clearer - user211576

3 answers 3

For MS SQL Server:

SELECT t.* FROM tbl t OUTER APPLY (SELECT TOP 1 intB FROM #tbl t2 WHERE t2.timeC<t.timeC ORDER BY t2.timeC desc) flt WHERE flt.intB<>t.intB OR flt.intB IS null

For each record, we take the previous one in time, if the value has changed or there is no previous record (the first record) - we output.

  • never heard of outer apply, go read. orakl also have it? - user211576 pm
  • If you believe Google - starting with 12c. I didn’t work with Oracle, so I’ll not say for sure. emrahmeteen.wordpress.com/2015/12/24/… - Zufir
  • Yes, I really liked this solution. as I understand it APPLY joins tables line by line. I tried to write something like this through join'y, did not work - user211576

MS SQL, Oracle, Postresql and other DBMS supporting window functions and in particular the lag () function returning the value from the previous line:

 select * from( select varA, intB, timeC, lag(intB) over(order by timeC) old_b from tbl ) A where intB!=old_b or old_b is null

If there are window functions in your version of SQL, but there is no LAG() function, then instead of it you can use, for example, avg(intB) over(order by timeC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) , i.e. get the average of the previous and current lines, which will be equal to the number in the current line if the previous one was the same.

MySQL unfortunately does not support window functions, but it does support variables:

 select * from( select varA, intB, timeC, @old_b old_b, @old_b:=intB xx from tbl,(select @old_b:=-1) A order by timeC ) A where intB!=old_b
  • very interesting, but on obsolete databases this function is not yet available - user211576
  • @ user211576 On obsolete, which ones? You can think of other ways, if there are other window functions, you can use them, for example, first_value, last_value, or even regular avg (), simply by specifying the window size minus one entry from the current one. You can also explicitly select the previous entry by a separate subquery in the selection list (and outer apply is not needed for this). But all methods depend on a specific DBMS and version (even a selection of the previous one, because in older versions often order by cannot be specified in a subquery). If you indicated them in the question I would write a request for the version you need. - Mike
  • I have here on SQL Server 2012 does not work, although MSDN writes that it is supported from 2012 - user211576
  • @ user211576 Does she exactly swear at her and how exactly? - Mike
  • @ user211576 Wrote in the answer a replacement, you must have AVG. I hope the means of indicating the size of the window is the same. - Mike

Try using triggers.

  • mean on hang up? No, I think slow. and in this case ALREADY there is a table - user211576