CODEWITHSUNDEEP
sqldatabasepostgresqljoin
java_new
java_new

Reputation: 83

Postgres:Get the latest record from a table

I have two tables which I use to fetch data and from this data I want to identify the records which are lastest related to a particular value. My first table is :

g_190048(
    id,
    logger_timestamp,
    message_id,
    begin_calc_timestamp ,
    intersection_id,
    matched_nr ,
    distance ,
    remainingphase_time ,
    current_phase ,
    advice_state,
    advice ,
    mode,
    CONSTRAINT g_190048_pk
    PRIMARY KEY (logger_timestamp , id )
)

"1001";1370253333921;190048;1546516;100;1;8028;36;1;0;-100;1
"1001";1370253334093;190048;1546719;100;1;7885;36;1;0;-100;1
"1001";1370253334593;190048;1547235;100;1;7885;36;1;0;-100;1
"1001";1370253334875;190048;1547516;100;1;7733;36;1;0;-100;1
"1001";1370253334906;190048;1547547;100;1;6631;35;1;0;-100;1
"1001";1370253335406;190048;1548047;100;1;6631;35;1;0;-100;1
"1001";1370253335687;190048;1548328;100;1;6306;35;1;0;-100;1
"1001";1370253335906;190048;1548532;100;1;5791;34;1;0;-100;1
"1001";1370253336234;190048;1548875;100;1;5518;34;1;0;-100;1
"1001";1370253336578;190048;1549157;100;1;5349;34;1;0;-100;1

Second table:

g_190049 (
    id,
    logger_timestamp,
    message_id,
    begin_calc_timestamp,
    msg1_recd_timestamp,
    msg2_recd_timestamp,
    msg1_end_timestamp,
    msg2_end_timestamp
)

"1001";1370253333921;190049;1546516;1546516;1546516;1546578;1546578;
"1001";1370253334093;190049;1546719;1546719;1546719;1546735;1546750;
"1001";1370253334593;190049;1547235;1547235;1547235;1547250;1547250;
"1001";1370253334890;190049;1547516;1547516;1547516;1547532;1547532;
"1001";1370253334921;190049;1547547;1547547;1547547;1547563;1547563;
"1001";1370253335421;190049;1548047;1548047;1548047;1548063;1548063;
"1001";1370253335703;190049;1548328;1548328;1548328;1548344;1548344;
"1001";1370253335906;190049;1548532;1548532;1548532;1548563;1548563;
"1001";1370253336250;190049;1548875;1548875;1548875;1548891;1548891;
"1001";1370253336578;190049;1549157;1549157;1549157;1549235;1549235;

I have a query, which joins the two tables and returns me msg1_recd_timestamp and remainingphase_timestamp

Query1:

SELECT
    g_190049.logger_timestamp,
    g_190049.msg1_recd_timestamp,
    g_190048.distance ,
    g_190048.remainingphase_time,
    g_190048.current_phase 
FROM g_190049 g_190049, g_190048 g_190048
WHERE
    g_190049.id = g_190048.id
    AND g_190049.begin_calc_timestamp = g_190048.begin_calc_timestamp
    AND g_190048.intersection_id = 100
    and g_190048.matched_nr = 1
    and g_190049.logger_timestamp BETWEEN '1370246100000' AND '1370253364000')

The ouput of this gives me:

    1370253333921   1546516  8028 36 1
    1370253334093   1546719  7885 36 1
    1370253334593   1547235  7885 36 1
    1370253334890   1547516  7733 36 1
    1370253334921   1547547  6631 35 1
    1370253335421   1548047  6631 35 1
    1370253335703   1548328  6306 35 1
    1370253335906   1548532  5791 34 1
    1370253336250   1548875  5518 34 1
    1370253336578   1549157  5349 34 1
    1370253336859   1549500  5167 34 1

But what I want is , latest row of the remainingphase_timestamp

    1370253334890   1547516  7733 36 1
    1370253335703   1548328  6306 35 1
    1370253336859   1549500  5167 34 1

If I have the output of query1 in a table, I can achieve my target using 

SELECT *
from TEMPO
where logger_timestamp IN (
    select max(logger_timestamp)
    from TEMPO
    Group by remaining_time
)

But I dont know how to integrate both the queries.

Upvotes: 1

Views: 494

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

select distinct on (remainingphase_time)
    g_190049.logger_timestamp,
    g_190049.msg1_recd_timestamp,
    g_190048.distance ,
    g_190048.remainingphase_time,
    g_190048.current_phase 
from
    g_190049
    inner join
    g_190048 using(id, begin_calc_timestamp)
where
    g_190048.intersection_id = 100
    and g_190048.matched_nr = 1
    and g_190049.logger_timestamp between '1370246100000' and '1370253364000'
order by remainingphase_time, g_190049.logger_timestamp desc

Upvotes: 1

Related Questions