Combine crosstab function with DISTINCT ON

Question

I have two tables details and data table. I already joined the two tables and the crosstab function is already done.

I want to show only the latest data per serial. Refer to the current and desired output below.

Question: How can I use DISTINCT ON in this crosstab query?

Table details:

serial   | date                 |    line      |   total_judgement
---------+----------------------+--------------+----------------
 123     | 2016/05/21 12:00:00  |      A       |       1
 456     | 2016/05/21 12:02:00  |      A       |       0
 456     | 2016/05/21 12:05:00  |      A       |       0

Table data:

serial   |     date             | readings   |   value
---------+----------------------+------------+-------------
 123     | 2016/05/21 12:00:00  | reading1   |  1.2342
 123     | 2016/05/21 12:00:00  | reading2   |  2.3213
 123     | 2016/05/21 12:00:00  | reading3   |  3.4232
 456     | 2016/05/21 12:00:02  | reading1   |  1.2546
 456     | 2016/05/21 12:00:02  | reading2   |  2.3297
 456     | 2016/05/21 12:00:02  | reading3   |  3.4264
 456     | 2016/05/21 12:00:05  | reading1   |  1.9879
 456     | 2016/05/21 12:00:05  | reading2   |  2.4754
 456     | 2016/05/21 12:00:05  | reading3   |  3.4312

Current Output:

serial   | line |      date            | total_judgement| reading1  |   reading2  |   reading3  
---------+------+----------------------+----------------+-----------+-------------+--------------
123      |  A   |  2016/05/21 12:00:00 |       1        |  1.2342   |   2.3213    |   3.4232      
456      |  A   |  2016/05/21 12:00:02 |       0        |  1.2546   |   2.3297    |   3.4264 
456      |  A   |  2016/05/21 12:00:02 |       0        |  1.9879   |   2.4754    |   3.4312

Desired Output:

serial   | line |     date             | total_judgement | reading1  |   reading2  |   reading3   
---------+------+----------------------+-----------------+-----------+-------------+--------------
123      |  A   |  2016/05/21 12:00:00 |         1       |  1.2342   |   2.3213    |   3.4232  
456      |  A   |  2016/05/21 12:00:05 |         0       |  1.9879   |   2.4754    |   3.4312

Here's my Code:

SELECT * FROM crosstab (
            $$ SELECT
                tb2.serial,
                tb1.line,
                tb2.date,
                tb1.total_judgement,
                tb2.readings,
                tb2.value
            FROM
                data tb2
                    INNER JOIN details tb1 ON (tb2.serial = tb1.serial 
             AND   tb2.date = tb1.date)
            ORDER BY tb2.date ASC $$,
            $$ VALUES ('reading1'),('reading2'),('reading3')$$
  ) as ct("S/N" VARCHAR (50),
          "Line" VARCHAR(3),
          "Date" TIMESTAMP,
          "TotalJudgement" CHARACTER(1),
          "Reading1" FLOAT8,
          "Reading2" FLOAT8,
          "Reading3" FLOAT8);

Notes

I need to join the two tables on serial and date.

I think DISTINCT ON may help with this but I don't seem to get correct results when I use DISTINCT ON serial.

score 1 · Accepted Answer · edited May 23 '17 at 12:08

Making some assumptions, this is probably what you are looking for:

SELECT * FROM crosstab (
   $$
   SELECT t2.serial
        , t1.line
        , t2.date
        , t1.total_judgement
        , t2.readings
        , t2.value
   FROM  (SELECT DISTINCT ON (serial) * FROM details ORDER BY serial, date DESC) t1
   JOIN   data t2 USING (serial, date)
   ORDER  BY t2.serial
   $$
 , $$VALUES ('reading1'),('reading2'),('reading3')$$
   ) AS ct("S/N" text
         , "Line" text
         , "Date" timestamp
         , "TotalJudgement" text
         , "Reading1" float8
         , "Reading2" float8
         , "Reading3" float8);

If you apply DISTINCT ON (serial) after the join, you would only retain a single row from data. Move the DISTINCT step into a subquery on details to get all readings in data for the latest row of each serial in details.

BTW, DISTINCT and DISTINCT ON ( expression [, ...] ) are not "functions" but SQL constructs. Basics:

Select first row in each GROUP BY group?

While being at it I made some simplifications to the code. Not essential for the answer.

If there are many rows per serial in table details, it might be more efficient to use one of these techniques instead of DISTINCT ON:

Optimize GROUP BY query to retrieve latest record per user

I can't be more specific without knowing table definitions, cardinalities etc.

Yes your assumption is right. I will try your code later, I'll update you and give feedback later. — tacticz03, May 23 '16 at 02:39
Thanks for the answer. It solved my problem. But btw, I'm trying to use trigger function which is every time there's an insert query on both table (table details and table data) should insert also on new table named, "output". I already have query for insert with crosstab function but I'm encountering problem. Maybe you can help me fixing this. I will post question on another thread maybe. — tacticz03, Jun 01 '16 at 14:12
@tacticz03: Yes, please post the separate question as separate question. Comments are not the place. You can always link to this one to provide more context if it's related. — Erwin Brandstetter, Jun 01 '16 at 14:18
Here's the other thread for mt question. Please see link. http://stackoverflow.com/questions/37658809/combine-crosstab-function-and-insert-new-row-only — tacticz03, Jun 06 '16 at 14:37

score 0 · Answer 2 · answered May 23 '16 at 02:16

Neither your code nor your data seem to match exactly with your "Current output". Perhaps you could get your desired output using DISTINCT ON in the following manner (inside your crosstab function):

SELECT DISTINCT ON (tb2.serial, tb2.readings)
       tb2.serial,
       tb1.line,
       tb2.date,
       tb1.total_judge,
       tb2.readings,
       tb2.value
FROM data tb2
JOIN details tb1 ON (tb2.serno = tb1.serno AND   tb2.date = tb1.date)
ORDER BY tb2.serial, tb2.readings, tb2.date DESC

But this would only be valid for your data if it is expected to follow the same principles as your sample data here. Otherwise, perhaps you can explain better what kind of data you have and what output you want.

Combine crosstab function with DISTINCT ON

Notes

2 Answers2