Here is a question I have been racking my brain over. Let us say I've got a table which has a number of timestamps along with a part number because the primary key. The table stores incremental changes, and therefore for each timestamp, if your area changes, that change is recorded. When the area does not change, then for that new timestamp it's NULL. Here's the fundamental idea.

 part | timestamp | x-pos | y-pos | status
------+-----------+-------+-------+--------
 a5   |       151 |     5 |    15 |      g
 a5   |       153 |  NULL |    17 |   NULL

(part, timestamp) may be the primary key. The NULLs within the second record indicate values which are unchanged because the first record.

What I wish to have the ability to do is choose the newest values for every area arranged through the part. For instance, because of the above records, the outcomes is going to be 153,5,17,g for part a5.

By now, I've this compromised together query.

    ((SELECT x-pos FROM part_changes WHERE x-pos IS NOT NULL
    ORDER BY timestamp DESC
    LIMIT 1)

    UNION

    (SELECT y-pos FROM part_changesWHERE y-pos IS NOT NULL
    ORDER BY timestamp DESC
    LIMIT 1)

    UNION

    (SELECT status FROM part_changes WHERE status IS NOT NULL
    ORDER BY timestamp DESC
    LIMIT 1))

But this returns just one column, meaning will be able to make use of a group-by for organizing.

There's got to become a more elegant method of doing factor, for example using COALESCE or perhaps is NULL inside an imaginative way. But I am stuck and should not decipher it. Anybody got a concept?

With no, I can not alter the database structure.

EDIT: ruakh has got the right idea. The only issue now's grouping by part. I can not appear to obtain round the LIMIT 1 for grouping by multiple parts. Any ideas?

mdahlman, I am much less acquainted with analytic functions in postgresql. So, in the event that solution could be simpler than the usual complex query, then go ahead and publish your idea.

EDIT 2: Thanks all for that help. I believe I have got a reasonable grasp of the items I have to do.

Instead of utilizing a UNION, it may sound like you want subqueries within the area list. That's, rather than (SELECT ...) UNION (SELECT ...) UNION (SELECT ...), you would like SELECT (SELECT ...), (SELECT ...), (SELECT ...).


For instance:

SELECT part,
       ( SELECT x_pos
           FROM part_changes
          WHERE part = pc.part
            AND x_pos IS NOT NULL
          ORDER
             BY timestamp DESC
          LIMIT 1
       ) AS x_pos,
       ( SELECT y_pos
           FROM part_changes
          WHERE part = pc.part
            AND y_pos IS NOT NULL
          ORDER
             BY timestamp DESC
          LIMIT 1
       ) AS y_pos,
       ( SELECT status
           FROM part_changes
          WHERE part = pc.part
            AND status IS NOT NULL
          ORDER
             BY timestamp DESC
          LIMIT 1
       ) AS status
  FROM ( SELECT DISTINCT
                part
           FROM part_changes
       ) AS pc
;

But at this time I'd really consider writing a saved procedure.


Alternatively:

SELECT DISTINCT
       part,
       FIRST_VALUE(x_pos) OVER
         ( PARTITION BY part
               ORDER BY CASE WHEN x_pos IS NULL
                             THEN NULL
                             ELSE TIMESTAMP
                         END DESC NULLS LAST
         ) AS x_pos,
       FIRST_VALUE(y_pos) OVER
         ( PARTITION BY part
               ORDER BY CASE WHEN y_pos IS NULL
                             THEN NULL
                             ELSE TIMESTAMP
                         END DESC NULLS LAST
         ) AS y_pos,
       FIRST_VALUE(status) OVER
         ( PARTITION BY part
               ORDER BY CASE WHEN status IS NULL
                             THEN NULL
                             ELSE TIMESTAMP
                         END DESC NULLS LAST
         ) AS status
  FROM part_changes
;

For just one part this will provide you with a solution .. because of ruakh

However I dont such as this version ..

SELECT 
    (SELECT timestamp  FROM part_changes WHERE part = $part 
    ORDER BY timestamp DESC
    LIMIT 1) as timestamp,

    (SELECT x-pos FROM part_changes WHERE part = $part and x-pos IS NOT NULL
    ORDER BY timestamp DESC
    LIMIT 1) as xpos,

    (SELECT y-pos FROM part_changes WHERE part = $part and  y-pos IS NOT NULL
    ORDER BY timestamp DESC
    LIMIT 1) as ypos,

    (SELECT status FROM part_changes WHERE part = $part and status IS NOT NULL
    ORDER BY timestamp DESC
    LIMIT 1)) as status