How to get the latest entry on same records?

[pwh]

Hi!

How to get the latest entry on same records? I have a table with records with the same itemcode but different entrydate(field). How can I query the latest item using entrydate?

The user select an item code or item codes from the list of item (listbox) to view it's inventory. What the result of the query should be the quantity of the selected itemcode/s. This query of mine displays all the entries of every items selected from the listbox. What I want is that only the latest entry of each item will be displayed using the entrydate field.. How can I add this argument to my query?

$SQL = "select a.itemcode, b.item_name, a.qty
from trn_detl as a, items as b
where a.itemcode=b.itemcode and a.cust_code=b.cust_code and a.cust_code='$custcode' and (";

if (sizeof($POST['invselect']) > 0)
{
foreach($POST['invselect'] as $key => $value)
{
$SQL .= " a.itemcode = '$value' OR";
}
} else {
$SQL .= $itemsrch;
}
$SQL = substr($SQL, 0, -2);
$SQL .= " ) ORDER by a.itemcode ";

Thanks in advance.

[nemonoman]

Something like

SELECT max(entrydate), itemcode
FROM trn_detl
WHERE custcode=1
GROUP BY itemcode

You just can add the other elements needed for the JOIN

[gtibok]

you could use limit

...ORDER BY a.itemcode DESC  LIMIT 0,1

[nemonoman]

The limit method you propose won't work for a join.

[pwh]

Originally posted by nemonoman
Something like

SELECT max(entrydate), itemcode
FROM trn_detl
WHERE custcode=1
GROUP BY itemcode

You just can add the other elements needed for the JOIN

Hi!

I tried you suggestion and it seems work a bit 🙂. It now displays the distinct item from the list but it didn't display the last entry (by entdate)... here is the modified code:

$SQL = "select max(a.entdate), a.itemcode, b.item_name, a.qty
from trn_detl as a, items as b
where a.itemcode=b.itemcode and a.cust_code=b.cust_code and a.cust_code='$custcode' and (";

if (sizeof($POST['invselect']) > 0)
{
foreach($POST['invselect'] as $key => $value)
{
$SQL .= " a.itemcode = '$value' OR";
}
} else {
$SQL .= $itemsrch;
}
$SQL = substr($SQL, 0, -2);
$SQL .= " ) GROUP BY a.itemcode ORDER by a.itemcode ";

What seems to be the problem?

Thanks again.