I am trying to run a query that will search through my database and pick out the first row for each session id. So sessions could have more than 1 row but I just want to select the first of each. Make sense? How can I do this?
In the table itself there is no order. There is no first, last, middle or 34th record. Think of it as a huge pile of rows of data all thrown together. Table records have no order. Queries, however, can have an order when you define one using the 'ORDER BY' clause. Is there a field in your table that you can use to order your query by? A timestamp field, a date field or an automatically incremented field? crivons method probably won't work. For one, I don't think you can use a 'GROUP BY' clause when you use the '*'. For another, grouping on just one field when you select all fields from your database will not leave you with just one record for each session id. Each unique row will appear in the result. Most likely you will need to run a subquery. First finding the session id and the MIN value of the field you are using to define your first record and then using those two fields back in your table to limit your query to. What fields are in the table? What fields do you want in your final query?
I have got a timestamp field that can be used to order by. Table fields: record_id accessed_time referrer ref_type ipaddress browserinfo se_query se_domain accessed_page date time session In my final query I want last pages accessed per session and a count on how often each was the last page. So I want: accessed_page, COUNT(accessed_page)
First, its not good to have fields called 'time' and 'date' in any table--those are function names and can screw things up when you run a query. Further, I think they are made redundant by the accessed_time field. Also, I think record_id might be a better field to use than the timestamp field. That is assuming it is an auto-incremented field, which I am going to assume and use. There are probably some MySQL query gurus who can get this into one query with some sub queries, but I would have to solve this programatically. First, I would then create a multi-dimensional array that will hold the page names and the number of times each page was the last one accessed. I would run this query to get the last record_id of each session: SELECT session, MAX(record_id) AS last_record GROUP BY session; PHP: I would then loop through every record in the results and run a query to find the accessed_page that corresponds to each last_record you found. Next, I would see if that accessed_page value is in the array that holds page names, if it is I would increment the corresponding value in the page count array, if not I would add that page name to the end of the page name array and add a new element to the end of the page count array whose value is 1. After you loop through every record of the first query you ran you would end up with two arrays like this: pagenames: ('index.htm', 'page2.htm', 'contactus.htm', 'thirdpage.htm') pagecounts: (12, 10, 4, 3) So in that example, the Contact Us page was the last page of people's session 4 times. This sure is long, so if you have any questions please let me know. And again, there might be someone who can help with a complex query with some subqueries that can get your results all in one sql statement.
Thanks for the response, I am just heading out but will give this a try when I get back in a few hours!