mysql query sorting and ordering

Question

Say i have a table called "users" and it has 40 rows of records, each row has fields:

id, firstname, group_id, login_count, stay_on_page_count

groups has

administrator (1), manager (2), employee (3)

is it possible to create a query that will sort and order the rows this way

    group _id      stay_on_page_count  login_count
    =========      ==================  ===========
    1              100mins             100
    1              90mins              90
    2              100mins             100
    3              100mins             100

    1              80mins              80
    1              70mins              70
    2              90mins              90
    3              90mins              90

    1              60mins              60
    1              50mins              50
    2              80mins              80
    3              80mins              80

    1              40mins              40
    1              30mins              30
    2              70mins              70
    3              70mins              70

Basically I would like to create a 4x4 grid view using the query result. the pseudo code is probably

SELECT all FROM user table and to group the result in to cluster of 4, 
    while each 4 should have ORDER BY group_id ASC as first priority (1,1,2,3) 
    AND stay_on_page_count ORDER BY DESC as second priority, 
    AND login_count ORDER BY DESC as last or third priority

i don't know if the pseudo code explains enough, but that's the only thing i can came up with :)

And if its possible, then will it sacrifice performance? Is there any better approach to accomplish this?

I am using Mysql and PHP (CakePHP 2.x)

Thanks

Reading your example output, I can't figure out the sorting condition. Could you explain? (pseudo-code is welcome) — RandomSeed
– RandomSeed, Commented Jun 6, 2013 at 9:58
you can do it in the PHP code itself. As per my knowledge there is no simple solution to sort the result as per columns. — skparwal
– skparwal, Commented Jun 6, 2013 at 10:07
I think that your sample output is wrong. I don't see group_id in the output for instance. Is "groups" another table or is "groups" actually "group_id"? Please, edit your question and make sure that all the info in your question is correct. — Mark
– Mark, Commented Jun 6, 2013 at 11:20
So you want to cluster into groups of 4 based on the stay_on_page_count, and then within those clusters, order by group_id? — Fabian Tamp
– Fabian Tamp, Commented Jun 6, 2013 at 11:36

user359040 · Accepted Answer · 2013-06-06 12:37:27Z

One approach (using summarised as a table holding the summarised values listed in the question):

select * from
(select s1.*, 
        @rank1:=@rank1+1 as rankcalc,
        floor(@rank1/2) rankgroup,
        @rank1%2 rankingroup
 from (select @rank1:=1) r1
 cross join
 (select * from summarised where group_id=1 order by stay_on_page_count desc) s1
 union all
 select s2.*, 
        @rank2:=@rank2+1 as rankcalc,
        @rank2 rankgroup,
        2 rankingroup
 from (select @rank2:=0) r2
 cross join
 (select * from summarised where group_id=2 order by stay_on_page_count desc) s2
 union all
 select s3.*, 
        @rank3:=@rank3+1 as rankcalc,
        @rank3 rankgroup,
        3 rankingroup
 from (select @rank3:=0) r3
 cross join
 (select * from summarised where group_id=3 order by stay_on_page_count desc) s3
) sq order by rankgroup, rankingroup

SQLFiddle here.

Note that this solution is dependent on the ordering being evaluated in the sequence specified in the sub-queries, and not overridden by the optimizer - this should work in current versions of MySQL, but may not work in MariaDB (the open source fork of MySQL) or future versions of MySQL.

xQbert · Accepted Answer · 2013-06-06 12:42:28Z

While this will generate the order you want it's very much a forced and hard coded effort. It makes several assumptions like login_count is always in the ranges you listed.

SELECT group_ID, Stay_on_Page_count, Login_Count, myset 
FROM (
SELECT group_ID, Stay_on_Page_Count, Login_Count, 1 as mySet
FROM USERS
WHERE (GROUP_ID=1 and Login_count >= 90) OR (group_ID in (2,3) and Login_Count=100)
UNION
SELECT group_ID, Stay_on_Page_Count, Login_Count, 2 as mySet
FROM USERS
WHERE (GROUP_ID=1 and Login_count Between 70 and 80) 
  OR (group_ID in (2,3) and Login_Count=90)
UNION
SELECT group_ID, Stay_on_Page_Count, Login_Count, 3 as mySet
FROM USERS
WHERE (GROUP_ID=1 and Login_count Between 50 and 60) 
  OR (group_ID in (2,3) and Login_Count=80)
UNION
SELECT group_ID, Stay_on_Page_Count, Login_Count, 4 as mySet
FROM USERS
WHERE (GROUP_ID=1 and Login_count Between 30 and 40) 
  OR (group_ID in (2,3) and Login_Count=70))
ORDER BY myset, group_ID, Login_count Desc

Collectives™ on Stack Overflow

mysql query sorting and ordering

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related