Order and sort result mysql or using php array funciton

Question

I have a table "student_points".

id    user_id     points   subject_id      
 1      10         45           22
 2      11         75           23
 3      12         78           24
 4      10         13           23
 5      12         65           23

and so on ...

This table contain about 1000 records of users and points. I want 10 records based on points (Max points first) So I can use mysql query as

Select * from student_points order by points limit 0,10

Now the requirement is that we need to group these 10 records based on user_id For example in first 10 records three are 3 students records so they should display in group. End result should be like

   id    user_id     points    subject_id  
    3      12         78           24
    5      12         65           23
    1      10         45           22
    4      10         13           23
    2      11         75           23

You can see that first record is based on most point and it student id is 12, now they are group according to user_id.

I tried two order by . I also tried to array_multisort after getting result but both are not working properly.

Please suggest any way, Either mysql query or group after getting result.

Thanks

Do you want 10 records for each user_id sorted by descending order of points? — 1000111
– 1000111, Commented Sep 21, 2016 at 10:47
Hello , I edited question for better understanding , now there is subject_id. those 10 results there can be records of 3 students based on points in different subject , I just want to group them based on user_id . — Rich5757
– Rich5757, Commented Sep 21, 2016 at 10:59

Hardik Patel · Accepted Answer · 2016-09-21 11:06:33Z

1

To get your required result I have written the query :

SELECT * FROM (
    Select * from student_points order by points limit 0,10
) As st
GROUP BY user_id,subject_id
ORDER BY points DESC

Please try this. Let me know if this is not work for you.

answered Sep 21, 2016 at 11:06

Hardik Patel

7166 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Storms786 · Accepted Answer · 2016-09-21 11:26:42Z

1

This should work just add a limit to whatever number you want to limit by

select sp.id, sp.user_id, sp.points 
from student_points sp
join (select user_id, max(points) as sort_by from student_points group by user_id) sort_table on sp.user_id = sort_table.user_id
order by sort_table.sort_by desc, sp.user_id, sp.points desc;

edited Sep 21, 2016 at 11:26

answered Sep 21, 2016 at 10:58

Storms786

4662 gold badges7 silver badges20 bronze badges

Comments

1000111 · Accepted Answer · 2016-09-21 10:54:21Z

0

I guess you need to wrap up your query in a subquery and later sort the results based on user_id and points.

SELECT * FROM 
(
  Select * from student_points order by points limit 0,10
) AS t
ORDER BY 
  t.user_id DESC, 
  t.points DESC

edited Sep 21, 2016 at 10:54

answered Sep 21, 2016 at 10:51

1000111

13.5k2 gold badges32 silver badges43 bronze badges

7 Comments

1000111 Over a year ago

I would love to hear the reason behind downvoting

Rich5757 Over a year ago

@1000111 , Hello please read question again, I have edited .

1000111 Over a year ago

Your requirement can still be achieved by this query. Please give this a try and let me know @Rich5757

jeroen Over a year ago

This will not show the student ID / group with the highest score first, so the conditions are not met completely.

1000111 Over a year ago

Maybe I am missing something. Actually I didn't get your message. Would you please post an answer here @jeroen

|

Collectives™ on Stack Overflow

Order and sort result mysql or using php array funciton

3 Answers 3

Comments

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related