SQL Query issue

I have an issue and not sure how to fix. My table is a "log" that detects the level of access an employee have at a given time. My table look like this

Id (Int) Auto increment
Name (varchar)
Logon_id (varchar)
Department (varchar)
Access_level (varchar)
Date_of_change (timestamp) CURRENT_TIMESTAMP

I want to generate a report that shows the current Access_level for each employee.
The employee should only be shown once, with the latest Date_of_change and the current Access_level at that date.

Any ideas? Thanks in advice

Regards
Rune

IThink you should be able to get what you are trying to acchieve using:

SELECT * FROM tbleNmae ORDER BY Date_of_change DESC GROUP BY Name

SELECT * FROM tbleName GROUP BY Name ORDER BY Date_of_change DESC

That's what I thought, but the problem is that it does not automatically give me the latest Date_of_change, because GROUP BY must come before ORDER BY

try using a nested query to order by date before grouping:

SELECT * FROM (SELECT * FROM tbleName ORDER BY Date_of_change DESC) as orderedTble GROUP BY Name

Your suggestion works when I run the query. My problem now is that I need to save the query as a view in MySQL, and the SELECT statement cannot contain a subquery in the FROM clause in a MySQL view. I need it as a view because I must filter the result even more by using URL parameters on my webpages.

Thats a limitation to MySQL

there really is no need to use a view for this, just to be able to filter using a URL variables, that can be done using a standard SQL query.

Ok, but when I'm going to filter using a URL variable I need to put the WHERE clause before GROUP BY like this

SELECT * FROM (SELECT * FROM tbleName ORDER BY Date_of_change DESC) as orderedTble
WHERE Access_level LIKE %colname%
GROUP BY Name

That does not automatically give me the correct result, because the Access_level is selected before GROUP BY.
I first need to run the query

SELECT * FROM (SELECT * FROM tbleName ORDER BY Date_of_change DESC) as orderedTble GROUP BY Name

Then filter by Access_level using a URL variable

add the where clause to the sub query:


SELECT * FROM (SELECT * FROM tbleName WHERE Access_level LIKE %colname% ORDER BY Date_of_change DESC) as orderedTble GROUP BY Name

Can not see the difference. The WHERE clause still runs before GROUP BY

I guess i dont follow what it is you are trying to accomplish and how the results of that query differs from the results you are trying to get.

please provide more details on the results you are getting versus the results you expect, if you could provide a dump of the table, that would be helpful too.