List the last record of a table field

Question

List the last record of a table field

Navigation

#1 by (1 votes)

0

I want to list the last record in the table and display, but I can not.

The reports table has the field registration date duration and destination_id

registration   date       time         duration         destination_id
    2501    2014-03-22  08:25:00  00:40:00(timestamp)      SBGL
    2501    2015-07-25  10:03:00  00:40:00(timestamp)      SBRJ
    5531    2015-09-15  19:24:00  00:40:00(timestamp)      SBSM
    5531    2015-10-19  10:15:00  00:40:00(timestamp)      SBBR

I want to show the last destination_id for registration 2501 and 5531. That is to say that:

2501 => SBRJ
5531 => SBBR

In the code below it returns:

2501 => SBGL
5531 => SBSM

Does anyone help pf?

$hours = mysql_query("SELECT SEC_TO_TIME( SUM( TIME_TO_SEC( duration ) ) ) AS duration_sum FROM reports GROUP BY registration");
$regist = mysql_query("SELECT registration AS regist FROM reports GROUP BY registration");
$model = mysql_query("SELECT equipment AS model FROM reports GROUP BY registration");
$base = mysql_query("SELECT destination_id FROM reports");

while($row = mysql_fetch_array($hours) AND $row2 = mysql_fetch_array($regist) AND $row3 = mysql_fetch_array($model) AND $row4 = mysql_fetch_array($base)){

<tr>
  <td align="center"><div align="left"><? echo($row3['model']); ?></div></td>
  <td align="center"><div align="left"><? echo($row2['regist']); ?></div></td>
  <td align="center"><div align="center"><? echo($row['duration_sum']); ?></div></td>
   <td align="center"><div align="center"><? echo($row4['destination_id']); ?></div></td>
</tr>
  <?php
        }//while

php mysql

asked by anonymous 10.11.2015 / 07:37

1 answer

Error writing database log record Rendered does not work on dataTable

score 1 · Accepted Answer

You need to change your query to get destination_id for the most recent date. This can be done as follows:

select registration, destination_id
from reports r
inner join 
( 
    select registration, max(timestamp('date', 'time')) maxT
    from reports
    group by registration
) MaxTime
  on MaxTime.registration = r.registration 
 and MaxTime.MaxT = timestamp('date', 'time')
order by 1

For the following structure

registration   date       time         duration                 destination_id
2501           2014-03-22  08:25:00    00:40:00(timestamp)      SBGL
2501           2015-07-25  10:03:00    00:40:00(timestamp)      SBRJ
5531           2015-09-15  19:24:00    00:40:00(timestamp)      SBSM
5531           2015-10-19  10:15:00    00:40:00(timestamp)      SBBR

The query will produce the following result:

registration   destination_id
2501           SBRJ
5531           SBBR

You can see the SQLFiddle