[Resolved] Need help to select Newest entry by date

Connexion

I have a mysql db that has multiple entry's for the same $SerialN, I need to select the newest entry by the date. $AssignedDate which is a TimeStamp NOW() entry

And not sure how to do it.

$query = "select * from assignacct where SerialN = '$Search1'";
				$result = mysql_db_query ("$dbname", $query)
					or die ("No $Search Found! Failed. Error: " . mysql_error());

Thanks for the help.

czambran

try this:

$query = "select * from assignacct where SerialN = '$Search1' order by datefield DESC limit 1"; //replace datefield with whatever is the name of the field with the timestamp.
                $result = mysql_db_query ("$dbname", $query)
                    or die ("No $Search Found! Failed. Error: " . mysql_error());

Connexion

Didn't work it is showing the box assigned to 2 different people.

Thanks for trying

czambran

I am sorry but I do not see how the select statement can return two values(did you see that I limited to just one)

Connexion

Sorry maybe I wasnt being clear enough it did work but it still brought up the oldest one.

Let me explain in more detail.

Here is my Table with the entry's

DateAssigned Serial AssignedTech id
2004-09-21 15:55:46 RAEGAP46899G 100101 1
2004-09-21 15:55:56 RBEDMP12079G 100101 2
2004-09-21 15:56:11 RXEDMP39686F 100101 3
2004-09-21 15:56:22 RAEGAP46893G 100101 4
2004-09-21 15:56:41 RAEDRP30497A 100101 5
2004-09-23 19:58:27 RAEDRP30497A 100102 6

Notice the last 2 entries Same Serial different AssignedTech.

The search I am trying to do is to see all Serial #'s assigned to a certain Tech ie: 100101 so all of the above should be selected except for the last one id # 5 as it was assigned on another tech on a newer date.

When the search runs for Tech 100101 it still showes.

Ok, while typing this I think I came up with a solution. As I was typing, it started to occur to me that what I wanted was not going to work.

Adding another field in the table a Y or N entry that will specify if the box is currently assigned to that tech or if it has been changed to a new tech.

What I am trying to do is keep a log of where all a serial # has been.

Thanks for the help.