3

i have a schema look like this:

CREATE TABLE users 
    (
     id int auto_increment primary key, 
     name varchar(20), 
     point int(255)
    );

INSERT INTO users
(name, point)
VALUES
('Jack', 1),
('Rick', 5),
('Danny', 11),
('Anthony', 24),
('Barla', 3),
('James', 15),
('Melvin', 12),
('Orthon', 5),
('Kenny', 2),
('Smith', 30),
('Steven', 27),
('Darly', 45),
('Peter', 44),
('Parker', 66),
('Lola', 78),
('Jennifer', 94),
('Smart', 87),
('Jin', 64),
('David', 31),
('Jill', 78),
('Ken', 48),
('Martin', 19),
('Adrian', 20),
('Oliver', 16),
('Ben', 100);

and my sql is:

select id, name, point from users Order by point desc, rand() LIMIT 5

problem is, my query does not select 5 row randomly and order them by point. Any idea, how to solve it? here is sqlfiddle: http://sqlfiddle.com/#!2/18f15/1

Redbox
  • 1,457
  • 5
  • 17
  • 22

2 Answers2

7
select id,name,point from
(select id, name, point from users Order by rand()  
LIMIT 5) abc
order by point desc;

SQLFIDDLE

Praveen Prasannan
  • 7,093
  • 10
  • 50
  • 70
2

problem is, my query does not select 5 row randomly and order them by point.

It's because in your given Query you are using ORDER BY clause. 

select id, name, point from users Order by point desc, rand() LIMIT 5

Try with removing point desc, in ORDER BY clause

select id, name, point from users Order by rand() LIMIT 5

SQL FIDDLE

Edit

select id,name,pont from
(select id, name, point from users Order by rand() LIMIT 5)temp
order by point desc

Note: There should be no Table with name temp in you DB (i.e. since you are using it in you alias)

Prahalad Gaggar
  • 11,389
  • 16
  • 53
  • 71