close ad
WARNING: Do Not Install the DREAMWEAVER CC 2017 Update »
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

SQL Query issue

Thread began 8/01/2013 2:57 pm by Rune Brynestad | Last modified 8/02/2013 5:57 pm by Rune Brynestad | 510 views | 10 replies |

Rune Brynestad

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

Sign in to reply to this post

Jason ByrnesWebAssist

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

Sign in to reply to this post

Rune Brynestad

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

Sign in to reply to this post

Jason ByrnesWebAssist

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

Sign in to reply to this post

Rune Brynestad

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.

Sign in to reply to this post

Jason ByrnesWebAssist

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.

Sign in to reply to this post

Rune Brynestad

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

Sign in to reply to this post

Jason ByrnesWebAssist

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

Sign in to reply to this post

Rune Brynestad

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

Sign in to reply to this post

Jason ByrnesWebAssist

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.

Sign in to reply to this post
loading

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...