خرید بک لینک

Vote count: 0

I have two tables details and data table. I already joined the two tables and the crosstab function is already done. My problem is I want a unique and last input serial.

In details table

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

In data table

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   |     date             | reading1  |   reading2  |   reading3 | total_judgement 
---------+----------------------+-----------+----- -------+-------------+--------------
123      |  2016/05/21 12:00:00 |  1.2342   |   2.3213    |   3.4232    |  1  
456      |  2016/05/21 12:00:02 |  1.2546   |   2.3297    |   3.4264    |  0
456      |  2016/05/21 12:00:02 |  1.9879   |   2.4754    |   3.4312    |  0

Desired Output:

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

Here's my Code:

SELECT
        *
    FROM
        crosstab (
            $$ SELECT
                tb2.serial,
                tb1.line,
                tb2.date,
                tb1.total_judge,
                tb2.readings,
                tb2.value
            FROM
                data tb2
                    INNER JOIN details tb1 ON (tb2.seo = tb1.seo 
             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);  

Note: In join query, I need to compare the serial and date

I think DISTINCT ON query may help me with this but i caot make it work when I used DISTINCT ON serial.

Please somebody help me with my issue. Thank you.

asked 1 min ago

برچسب: نویسنده: استخدام کار تاريخ: دوشنبه 3 خرداد 1395 ساعت: 8:50

صفحه بندی