Gary, The if() function often allows you to replace a union query. For example this may work for you - watch the LIMIT it operates AFTER the sort so your sort needs to put the records which you want at the front:
SELECT user_id, username, last_login, if(last_team = 380, 1, 2) as SortOrder FROM users WHERE unix_timestamp(last_access) > unix_timestamp()-1440 AND user_id <> '9' ORDER BY SortOrder, last_login LIMIT 20); Regards, Andy Gary Broughton wrote: > > Hi all > > > > I want to provide a list of up to 20 online users on our network of > football forums, but would like to list those live on the current team > first, before "filling" any remainder with those online using a > different team. I couldn't see any way of getting it all into one > select (which in English 'speak' would be like "order by team 380, then > get the rest" I suppose?), and saw only the UNION function as the > possible solution. > > > > All I'm after, if possible, is to know if I'm using the most efficient > method of retrieving the data, and also whether putting the extra "LIMIT > 20" outside the UNION would indeed pick up the first 20 records only, > even though there's a potential for 40. > > > > (SELECT user_id, username, last_login FROM users > > WHERE unix_timestamp(last_access) > unix_timestamp()-1440 > > AND user_id <> '9' AND last_team = '380' ORDER BY last_login LIMIT 20) > > UNION > > (SELECT user_id, username, last_login FROM users > > WHERE unix_timestamp(last_access) > unix_timestamp()-1440 > > AND last_team <> '380' ORDER BY last_login LIMIT 20) > > LIMIT 20 > > > > Incidentally, the "user_id <> '25'" is only there to prevent display of > the name of the current online user in the list. > > > > Many thanks as always > > Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
