0

So im creating a integration script, but for some reason its not showing the exprected result. My query is

SELECT xf_user_group_relation.user_id, xf_user_group_relation.user_group_id, MAX( xf_user_group.display_style_priority ) AS display_style_priority
FROM xf_user_group_relation
INNER JOIN xf_user_group ON xf_user_group_relation.user_group_id = xf_user_group.user_group_id
WHERE xf_user_group.display_style_priority >=1000
GROUP BY user_id

One of the lines in the result which is unexpected is this

user_id | user_group_id | display_style_priority    
86 | 11 |5200

The group with the id 11 has display_style_priority 2000 and not 5200. It should show the user group id with display_style_priority 5200. Which is the highest ranked group this user is in. Can somebody point out what im doing wrong.

BlackVoid
  • 627
  • 1
  • 6
  • 21

1 Answers1

4

Try changing the GROUP BY to 

GROUP BY
   xf_user_group_relation.user_id, xf_user_group_relation.user_group_id

Most other DBMS would throw an error for that query because every column in the SELECT list must be either aggregated or in the GROUP BY.

Edit: this is a great example of why ANSI-SQL and other RDBMS do not allow this syntax

gbn
  • 422,506
  • 82
  • 585
  • 676