mySQL "get TOP 100 scores" query is turning me crazy

Question

I have asked this question Friday and since then I have been trying everything I could think of with no success

I cannot get this to work

SELECT * FROM WorldFlowers_table GROUP BY device_id ORDER BY score DESC LIMIT 100

and return me :

the 100 rows with the top 100 scores
filtered so that only the highest score of each device_id is featured
ORDER BY score DESC

@ 1000111 and Giorgos Betsos

A guy comes into a room, says, "please help, I'm getting crazy trying with all my forces to solve a simple problem that any of you could solve in the blink of an eye"

2 guys turn around, shoot the guy in the face and say "duplicate"

I am getting exhausted :(

///////// to adress Strawberry comment

I tried building that in SQLFiddle

CREATE TABLE WorldFlowers_table 
    (
     id int identity primary key, 
     timestamp varchar(20), 
     name varchar(30),
     score int,
     color varchar(30),
     flower varchar(30),
     device_id varchar(30),
    );

INSERT INTO WorldFlowers_table
(timestamp, name,score, color, flower, device_id  )
VALUES
('1475151826', 'RI-RI', 42, '0|0|0|0|0|0|0|0|0|0', '[1475152768|42|4|0.0]', 'XYZ' ),
('1475151826', 'RO-RO', 46, '0|0|0|0|0|0|0|0|0|0', '[1475152768|42|4|0.0]', 'ABC' ),
('1475151826', 'RI-RI', 42, '0|0|0|0|0|0|0|0|0|0', '[1475152768|42|4|0.0]', 'XYZ' ),
('1475151826', 'RA-RA', 45, '0|0|0|0|0|0|0|0|0|0', '[1475152768|42|4|0.0]', 'ABC' ),
('1475151826', 'RU-RU', 42, '0|0|0|0|0|0|0|0|0|0', '[1475152768|42|4|0.0]', 'XYZ' ),
('1475151826', 'RE-RE', 44, '0|0|0|0|0|0|0|0|0|0', '[1475152768|42|4|0.0]', 'DEF' ),
('1475151826', 'RY-RY', 42, '0|0|0|0|0|0|0|0|0|0', '[1475152768|42|4|0.0]', 'XYZ' ),
('1475151826', 'RX-RX', 43, '0|0|0|0|0|0|0|0|0|0', '[1475152768|42|4|0.0]', 'XYZ' ),
('1475151826', 'RA-RA', 42, '0|0|0|0|0|0|0|0|0|0', '[1475152768|42|4|0.0]', 'DEF' );

but is does only builds one time out of 2.

The return I am looking for would then be

+------+------------+--------------------------------------------+
| id   | timestamp  | name  | score | color | flower | device_id |                               
+------+------------+--------------------------------------------+
| 2    | blabla     | RO-RO |  46   |  ...  |   ...  |   ABC     | 
| 5    | blabla     | RE-RE |  44   |  ...  |   ...  |   DEF     |
| 7    | blabla     | RX-RX |  43   |  ...  |   ...  |   XYZ     |
+------+-------+-------------------------------------------------+

. only 1 of each device-id per return

. the highest score per device-id

. and the result being given in ORDER BY score DESC

UPDATE: this from scaisEdge seems to work fine

SELECT * FROM scores WHERE (name, score) IN ( SELECT name, MAX(score) FROM scores GROUP BY name
ORDER BY score DESC ) ORDER BY score DESC LIMIT 100 ;

See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Oct 03 '16 at 14:09
it doesn't really matter , my point was just to say that I need all the columns on the row. The answer from scaisEdge seems to work fine :] — RichGG, Oct 04 '16 at 04:34

ScaisEdge · Accepted Answer · 2016-10-04T06:58:43.197

1

If i undestand right you want the top 100 highest device score

  SELECT *
  FROM WorldFlowers_table
  WHERE (device_id, score) IN (SELECT device_id, max(score) 
                               FROM WorldFlowers_table 
                               GROUP BY device_id )
  ORDER BY score DESC 
  LIMIT 100

edited Oct 04 '16 at 06:58

answered Oct 03 '16 at 14:28

ScaisEdge

131,976
10
91
107

"#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'" :S – RichGG Oct 03 '16 at 14:31
but, removing the LIMIT that is inside the second SELECT might (!) be the answer – RichGG Oct 03 '16 at 14:33
I have update the answer (the limit in the subquery can be avoid) .. then try and let me know .. and if the answer is right please mark it as accepetd .. – ScaisEdge Oct 03 '16 at 14:35
testing and testing, I wish I could tell you that this is it, but struggling with that thing for so long, I afraid say success and see, after more testing, that there is so much work left on the table In the mean time I want to tell you thank you. It means a lot to me that someone is kind enought to help me that way cheers – RichGG Oct 03 '16 at 14:46
@RichGG . if the query work .. then .. what's the problem ..the logic is clear and (for me) simple .. the subselect return the couple of device_id and the related max(score) and then using this set of tuple .. you filter your table ... from the result you get only the first 100 .. I repeat .. (for me ) is simple – ScaisEdge Oct 03 '16 at 14:50
As far as my testing goes it works fine :) but I'm waiting for my friends to send more queries through my alpha app so that I can finally say "I'm through with sql" It as been such a headache – RichGG Oct 04 '16 at 04:28
@RichGG . try use a simple Eulero Venn diagramm based based on the data involved .. – ScaisEdge Oct 04 '16 at 05:47
could you sort of break down the query to make it clearer ? I understand : * we SELECT all from "big subset" and order it by score * "big subset" is made of a Selection grouped by device_id and ordered by score * but we only take part of this "small subset", using WHERE – RichGG Oct 04 '16 at 06:52
@RichGG . i have update the asnwer because the order by in subselect is unuseful .. ..1) I select the device_id and the related max value .. grouping by device id .. so i have a distinct list of device_id with the max value .. 2) from the main table i filter the rows that mathc ths subselect value (same device_id and score alias max score) and from all the rows i order by score a get the first 100 . hope is clear – ScaisEdge Oct 04 '16 at 07:02

score 0 · Answer 2 · edited May 23 '17 at 10:27

MySQL does not support LIMIT in subqueries for certain subquery operators:

mysql> SELECT * FROM t1
    ->   WHERE s1 IN (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1);
ERROR 1235 (42000): This version of MySQL doesn't yet support
 'LIMIT & IN/ALL/ANY/SOME subquery'

https://dev.mysql.com/doc/mysql-reslimits-excerpt/5.6/en/subquery-restrictions.html

Take a look here:

Problem with LIMIT & IN/ALL/ANY/SOME subquery

score 0 · Answer 3 · answered Oct 04 '16 at 01:22

0

SELECT id, timestamp, name, score, color, flower, device_id
    FROM WorldFlowers_table 
    WHERE (device_id, score) IN 
    (SELECT device_id, MAX(score) FROM WorldFlowers_table GROUP BY device_id)
ORDER BY score DESC LIMIT 100;

answered Oct 04 '16 at 01:22

Sonam Gurung

117
1
3

mySQL "get TOP 100 scores" query is turning me crazy

3 Answers3

Linked