2

I have a table "measurements" which looks like this (with empty cells in the last col)

ser_nr | meas_ser | meas_value | last_meas
-------+----------+------------+-----------
  A1   |    1     |    12      |  (False)
  A1   |    2     |    5.5     |  (False)
  A1   |    3     |    5.12    |  (False)
  A1   |    4     |    5.01    |  (True)
  B1   |    1     |    2       |  (False)
  B1   |    2     |    2.03    |  (True)

http://sqlfiddle.com/#!15/bcd6b (Link for create table etc.)

I need this to query "select ... where last_meas=True"

I wrote a trigger for "after insert" and this works for the newly inserted rows. But what can I do to update the whole table?

For example: I want to delete the A1 row with meas_value "5.01". There has to be a function which executes for the whole remaining table and updates the last_meas column (in this example the third row should be True now). I can trigger a function using "after delete for each row" but using this method i can only access all deleted rows.

I found a workaround by generating a view using

SELECT * FROM measurements AS m LEFT OUTER JOIN (SELECT m.ser_nr, 
    max(m.meas_ser) AS meas_ser, TRUE AS last_meas FROM measurements AS 
    m GROUP BY m.ser_nr ) AS last_ms USING (ser_nr, meas_ser);

but this only generates "True" and not "False".

Is there a more easy way doing this?

  • Why do you need to have the last_meas as a column in the table? And not find the last row, each time you need it? – ypercubeᵀᴹ Nov 9 '16 at 16:14
  • In reality the table is much longer. For every serial number there are between 2 and 20 measurements. After every measurement the difference between the last and the one before the last is checked. If the difference is too much there will be an additional meas. For most usage I just need the values from the last measurement, but it is also interesting to have more data (how many meas. average, are there values being too big to be realistic etc.) – CherrySkizz Nov 9 '16 at 16:24
  • OK but that doesn't mean you have to store which row is the last one per serial number (because then you need additional overhead to update and scan the whole table every time you do inserts or deletes). You can easily calculate/find the last or the last 2 rows with window functions. – ypercubeᵀᴹ Nov 9 '16 at 16:29
  • I already assumed that a trigger would be too much, I just wanted to hear the thoughts of other users to this specific problem. For anyone wondering: a query using a window function would look like this select t.ser_nr, t.meas_ser, t.meas_value from (select ser_nr, meas_ser,meas_value,rank() OVER (PARTITION BY ser_nr ORDER BY meas_ser DESC) FROM measurements) as t where t.rank=1; – CherrySkizz Nov 9 '16 at 17:03
0

You can use this simpler / cheaper query for your view or CTE or simply a subquery:

SELECT ser_nr, meas_ser, meas_value
     , row_number() OVER (PARTITION BY ser_nr ORDER BY meas_ser DESC) = 1 AS last_meas
FROM   measurements;

SQL Fiddle.

There are no corner case problems with duplicate or NULL values here while you have that PK constraint you added in the fiddle:

ALTER TABLE measurements ADD CONSTRAINT measurements_pk PRIMARY KEY (ser_nr,meas_ser);

The associated index will also help to make it fast if you only need a small selection of ser_no from a big table.

And yes, this dynamic approach has so much less potential for headache (consistency in the face of concurrent write access!) than trying to keep rows in your base table current.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.