Databases
StackOverflow2013
Postsdbo
POPostgres:Get the latest record from a table
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPostgres:Get the latest record from a table
    primarykey
    data
    text
    <p>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 :</p> <pre><code>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 </code></pre> <p>Second table:</p> <pre><code>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; </code></pre> <p>I have a query, which joins the two tables and returns me msg1_recd_timestamp and remainingphase_timestamp </p> <p>Query1:</p> <pre><code>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') </code></pre> <p>The ouput of this gives me:</p> <pre><code> 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 </code></pre> <p>But what I want is , latest row of the remainingphase_timestamp</p> <pre><code> 1370253334890 1547516 7733 36 1 1370253335703 1548328 6306 35 1 1370253336859 1549500 5167 34 1 </code></pre> <p>If I have the output of query1 in a table, I can achieve my target using </p> <pre><code>SELECT * from TEMPO where logger_timestamp IN ( select max(logger_timestamp) from TEMPO Group by remaining_time ) </code></pre> <p>But I dont know how to integrate both the queries.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USClodoaldo Neto
    1. USjava_new
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POPostgres:Get the latest record from a table
      1. USjava_learner
      1. VTFavorite
    2. VO
      singulars
      1. POPostgres:Get the latest record from a table
      1. This table or related slice is empty.
      1. VTUpMod
    1. COLook into `order by` and `limit`. And `join`.
      singulars
      1. POPostgres:Get the latest record from a table
      1. USDenis de Bernardy
    2. COtry 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') ;`
      singulars
      1. POPostgres:Get the latest record from a table
      1. USAnantha Sharma
    3. COAnantha, add it as answer.
      singulars
      1. POPostgres:Get the latest record from a table
      1. USBurhan Khalid
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload