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.
