Why does adding GROUP BY always return the MAX?

  • Resolved SteveYantz

    (@steveyantz)


    14 years, 4 months ago

    Even if I apply MIN() to a field in a select statement. The get_row() always returns the max when hosting a GROUP BY.
    it’s almost like adding GROUP BY ignores any MAX() or MIN() code and solely returns the max of the query.

    [oh, thought I should also key this information, I’m working with php queries $wpdb->get_row()]

    [I’m also calling post’s based on category, then simply grouping them by the category to get one result: here’s the code]

    $NSeason = $wpdb->get_row("
	SELECT MIN(post_episode) as post_episode, post_title
	   FROM $wpdb->posts key1
	INNER JOIN $wpdb->term_relationships key2
            on key1.ID = key2.object_id
	WHERE key2.term_taxonomy_id = $thiscat->id
	   AND key1.post_status = 'publish'
	   AND key1.post_type = 'post'
	   AND key1.post_season = $post->post_season + 1
	GROUP BY key2.term_taxonomy_id
");
Viewing 3 replies - 1 through 3 (of 3 total)

  • Do you have access to some SQL management software for your server? I would suggest trying your query through their in order to see what sort of results you get. This will prove if it is your query or WordPress overriding your query which is causing the problem.

    You might also try adding “, ARRAY_N);” to the end of your get_row call and then var_dump-ing the data out to the screen to see what sort of information is being returned. It is possible that the query is returning more data than you expect (but not likely).

    Thread Starter SteveYantz

    (@steveyantz)

    Thanks for the help. I figured it out. Withouth the restrictions like GROUP BY or MIN, it showed the rows exactly as expected. For some reason the GROUP BY didn’t like me organizing it by a foreign table? Simply changing GROUP BY key2.term_taxonomy_id
    to GROUP BY key1.post_status, fixed the problem.

    Nice find. I totally missed that in your code, so I am glad that you found it!

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘Why does adding GROUP BY always return the MAX?’ is closed to new replies.