MAX Date

[Donn]

Hi,

I have the below query and it works, however I need the emp.Date to be MAX Date as I want the last time that the person logged in, the below code shows the first time they logged in.

Could anyone please help, I have tried and got good advise already but stuck on this last hurdle.

cheers


<?
$query = "SELECT emp.Date, emp.Passcode, emp2.Surname, emp.status
FROM emp INNER JOIN emp2 ON emp.Passcode = emp2.Passcode GROUP BY emp.Passcode ";

$result = mysql_query($query);
$newline = "<br />";
while($row = mysql_fetch_array($result)){
echo "". $row['Passcode']." " .$row['Surname']. " " .$row['Date']." " .$row['status'];
echo "<br />";
}
?>

[fenway]

So why not:

[code]
SELECT MAX(emp.Date) AS maxDate, emp.Passcode, emp2.Surname, emp.status
FROM emp INNER JOIN emp2 ON emp.Passcode = emp2.Passcode GROUP BY emp.Passcode
[/code]

[Donn]

[quote author=fenway link=topic=118882.msg486218#msg486218 date=1166292403]
So why not:

[code]
SELECT MAX(emp.Date) AS maxDate, emp.Passcode, emp2.Surname, emp.status
FROM emp INNER JOIN emp2 ON emp.Passcode = emp2.Passcode GROUP BY emp.Passcode
[/code]
[/quote]

Hi,
This does not work,

I used to get a result like
1111 Hutchinson 2006-12-14 22:33:07 In
2222 Corkish 2006-12-12 23:02:56 Out
3159 Houldsworth 2006-12-14 07:47:18 Out
3333 Kelly 2006-12-14 22:33:27 Out
14789 Fox 2006-12-15 10:30:39 In

Now I get
1111 Hutchinson In
2222 Corkish Out
3159 Houldsworth Out
3333 Kelly Out
14789 Fox In

it still shows the first record in the table not the last of each person also no time?

|I am really stumped on this. Any help appreciated.

[artacus]

Well your field name changed from Date to maxDate so if you didn't account for that, your dates wont show up.