i looking special kind of requirement query on mysql database want apply order by in different manner. e.g. in db fields user_id , user_rating , user_department following data.
+------------------------------------------------------+ | user_id | user_rating | user_department | +------------------------------------------------------+ | 1 | 102 | | | 2 | 33 | b | | 3 | 43 | c | | 4 | 54 | | | 5 | 63 | | | 6 | 214 | b | | 7 | 82 | | | 8 | 87 | c | | 9 | 43 | | | 10 | 98 | | | 11 | 73 | c | | 12 | 31 | | +------------------------------------------------------+ given above structure want sort results in order of user_rating each_department , need @ max 5 records each user_department among intial 3 records should in order of rating rest 2 should random.
so in above case output similar to:
+------------------------------------------------------+ | user_id | user_rating | user_department | +------------------------------------------------------+ | 1 | 102 | | | 10 | 98 | | | 7 | 82 | | | 12 | 31 | | | 5 | 63 | | | 6 | 214 | b | | 2 | 33 | b | | 8 | 87 | c | | 11 | 73 | c | | 3 | 43 | c | +------------------------------------------------------+ i tried options available on web custom order by, using field function couldn't find useful here. tried solve using subquery option not feasible mysql not allow me use in , limit keywords in query.
is there better/simpler way solve this.
this isn't looking offers 1 approach.
the idea use group_concat() put top 5 values each department in single column. column has form:
user_id:rating repeated 5 times, separated comma. in:
1:182,10:98,7:82,12:31,5:63 the query is:
select user_department, substring_index(group_concat(concat(user_id, ':', user_rating) order user_rating desc ), ',', 5) t group user_department; this not handle randomization of last 2 values. , puts in 1 row. but, thought might help.
my next attempt technically ask for, runs risk last 2 "random" users same.
it uses same group_concat() trick above. however, goes beyond choosing different values list, using substring_index():
select u.user_id, user_id.user_rating, u.user_department (select (case when n.n in (1, 2, 3) or ud.numusers <= 5 cast(substring_index(substring_index(users, ',', n.n), ',', -1) unsigned) else cast(substring_index(substring_index(users, ',', 4 + rand()*(num_users - 3)), ',', -1) unsigned) end) user_id (select user_department, group_concat(user_id order user_rating desc) users, count(*) numusers t group user_department ) ud join (select 1 n union select 2 union select 3 union select 4 union select 5 ) n on n.n <= ud.numusers ) u join t on u.user_id = t.user_id order user_department, user_rating desc 
Comments
Post a Comment