Welcome to WebmasterWorld Guest from 54.159.214.27

Forum Moderators: open

Mysql: How do I get GROUP BY to pick the right record.

   
8:43 am on Jul 18, 2007 (gmt 0)

10+ Year Member



Hi,

The below statement works as expected apart from the GROUP BY command which causes me to not get the latest visit_date from the track_page table. Obviously the ORDER BY is processed after GROUP BY, if GROUP BY would pick the first item from each group that was in date order it would pick the most recent date. Is there a way to do this other than having separate SQL statements?

SELECT p.session_index, p.page_type, p.reference, p.visit_date, s.session_id, s.ip, s.last_order_id
,(SELECT count(*) FROM bcl_track_page
WHERE session_index = s.session_index AND visit_date >= date_sub('2007-07-18 09:32:45',interval 24 hour)) AS pages FROM bcl_track_page AS p
INNER JOIN bcl_track_session AS s ON s.session_index = p.session_index WHERE s.last_visit_datetime >= date_sub('2007-07-18 09:32:45',interval 24 hour) AND (s.session_type = 'C' OR s.session_type = 'M')
GROUP BY p.session_index
ORDER BY p.visit_date DESC
LIMIT 0,10

Obviously, I found I could insert a subselect to get the row from track_page, but that would be silly to have for each field I required like the last visit_date... e.g.

(SELECT reference FROM bcl_track_page WHERE session_index = s.session_index AND visit_date >= date_sub('2007-07-18 09:32:45',interval 24 hour) ORDER BY visit_date LIMIT 0,1) AS last_reference

 

Featured Threads

My Threads

Hot Threads This Week

Hot Threads This Month