Friday, June 21, 2013

SQL. Sort in a Group

I was looking for a SQL statement to sort the data in a group. For example I have a table "logs". In "logs" I keep the users visits. If you log in into the system I record the event with the date and time. Very often I have been asked to answer the question when the users visited the site for first time or when for last time. So I need to create a list of user names and next to it the date and time for the first or last visit. So far I was doing this using stored procedures or in a program. Recently my friend Victor showed to me the solution bellow. I don't know if that actually works for my case.
You can find the original post here: http://bin.cakephp.org/view/1153419505

                FROM(
                SELECT Message.*, Job.*, Sender.*
                FROM (
                (messages as Message
                LEFT JOIN users as Sender on Message.sender_id = Sender.id)
                LEFT JOIN  
                (jobs_messages as JobsMessage LEFT JOIN jobs as Job ON JobsMessage.job_id = Job.id)
               
                on Message.id = JobsMessage.message_id
               
                )                
                ORDER BY Message.id DESC
                ) as result
                GROUP BY Job.id, Sender.id

No comments:

Post a Comment