Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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.

share|improve this question
    
Look into order by and limit. And join. –  Denis Jun 18 '13 at 11:58
    
try something like ... 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 right outer join g_190048 g_190048 on g_190049.id = g_190048.id WHERE 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') ; –  Anantha Sharma Jun 18 '13 at 12:11
    
Anantha, add it as answer. –  Burhan Khalid Jun 18 '13 at 12:21

1 Answer 1

up vote 1 down vote accepted
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
share|improve this answer
    
Thankyou for the answer –  java_new Jun 18 '13 at 12:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.