homepage Welcome to WebmasterWorld Guest from 174.129.80.166
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe and Support WebmasterWorld
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

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




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

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

 

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved