[Resolved] Select Latest Record for EACH Username

[partypete]

Hello,

I've left joined two tables. I now want to retrieve only the latest record(highest punch_id) for EACH username(punch_username). So if after the left join, I have a table with the following:

punch_id     punch_username     employee_username     employee_fullname
1            bob                bob                   Bob Smith
2            steve              steve                 Steve Jacobs
3            steve              steve                 Steve Jacobs
4            janet              janet                 Janet Kye
5            bob                bob                   Bob Smith
6            janet              janet                 Janet Kye
7            steve              steve                 Steve Jacobs
8            bob                bob                   Bob Smith
9            steve              steve                 Steve Jacobs
10           steve              steve                 Steve Jacobs

Using the WHERE clause(I think) I'd like ONLY the following records to be returned:

8            bob                bob                  Bob Smith
10           steve              steve                Steve Jacobs
6            janet              janet                Janet Kye

This is a simplified version of my SQL statement(The full statement has fields which are time sensitive which is why I want the latest records):

SELECT time_punch.punch_id, time_punch.punch_username, time_employees.employee_username, time_employees.employee_fullname
FROM time_punch LEFT JOIN time_employees ON time_punch.punch_username=time_employees.employee_username

Is there a way to get the latest record(using punch_id) for each username(using punch_username)?

Thank you!

Peter

[tomhath]

It depends on the DBMS.With most (except for mysql 3.2x) it's easiest to use a subselect:

SELECT tp1.punch_id, tp1.punch_username, te.employee_username, te.employee_fullname 
FROM time_punch tp1
LEFT JOIN time_employees te 
ON tp1.punch_username=te.employee_username
WHERE NOT EXISTS (
  SELECT 1 FROM time_punch tp2  

  WHERE tp1.punch_username = tp2.punch_username 
    AND tp1.punch_id < tp2.punch_id)

With mysql 3.23 you can get the same effect with a strange looking left join:

SELECT tp1.punch_id, tp1.punch_username, te.employee_username, te.employee_fullname 
FROM time_punch tp1
LEFT JOIN time_employees te 
ON tp1.punch_username=te.employee_username
LEFT JOIN time_punch tp2  

ON tp1.punch_username = tp2.punch_username 
  AND tp1.punch_id < tp2.punch_id
WHERE tp2.punch_id IS NULL

[Sxooter]

could you do something like:

select max(record), date, id from table group by id, date

???

[johnnyv]

I wouldn't have a clue, looks like you would need subqueries to do it.

I would do a query to get the employee name and id put them in an array then foreach name in the array do a query on the punch table

<?
$sql = "select employee_username,employee_fullname from time_employee";
$results = mysql_query($sql,$connection);
$employee_names = array();
while($row = mysql_fetch_assoc($results))
{
$employee_names[$row['employee_username']] = $row['employee_fullname'];
}

$employee_punch = array();
foreach($employee_names as $key => $value)
{
$sql = "select punch_id from time_punch where punch_username = \"$key\" order by punch_id desc limit 1";
$results = mysql_query($sql,$connection);
	while($row = mysql_fetch_assoc($results))
	{
	$employee_punch[$key] = $row['punch_id'];
	}
echo "Employee $value's last punch id was {$employee_punch[$key]}<br>";
}

?>

Not tested so probably some syntax errors in there, but you should get the idea.
Probably not the best way but i never said i was good 😉

[partypete]

Thanks all! You guys are always fantastic.

Actually tomhath's second example seems to work good right out of the box 🙂

Unfortunately the first example gives me a syntax error somewhere after WHERE NOT EXISTS (. I'm using mysql 4.015.

But since one of the examples works, I'm more than happy!

Peter