CODEWITHSUNDEEP
mysqlsqldatabasesql-order-by
TheGreatCornholio
TheGreatCornholio

Reputation: 1455

MySQL get rows starting with specific id after sort / order by

I got this table:

id | score
1  | 1
2  | 4
3  | 4
4  | 3
5  | 2
6  | 2
7  | 1
8  | 4
9  | 2
10 | 3

I need to order it by score desc:

id | score
2  | 4
3  | 4
8  | 4
4  | 3
10 | 3
5  | 2
6  | 2
9  | 2
1  | 1
7  | 1

and get first 3 rows which starts with id 6

So the result should be:

6  | 2
9  | 2
1  | 1

Is this possible? Thanks in advance

Upvotes: 2

Views: 1502

Answers (3)

forpas
forpas

Reputation: 164069

With this:

select t.*
from tablename t cross join (select * from tablename where id = 6) c
where t.score < c.score or (t.score = c.score and t.id >= c.id)
order by t.score desc, t.id
limit 3

See the demo.
Results:

| id  | score |
| --- | ----- |
| 6   | 2     |
| 9   | 2     |
| 1   | 1     |

Upvotes: 1

nbk
nbk

Reputation: 49373

With this table 

CREATE TABLE table3
    (`id` int, `score` int)
;

INSERT INTO table3
    (`id`, `score`)
VALUES
    (1, 1),
    (2, 4),
    (3, 4),
    (4, 3),
    (5, 2),
    (6, 2),
    (7, 1),
    (8, 4),
    (9, 2),
    (10, 3)
;

And this select

SELECT `id`, `score`
FROM (SELECT `id`,`score`,if (id = 8,@scoreid:= @scoreid +1,@scoreid) scoreid
       From table3, (SELECT @scoreid :=0) s  Order by score desc) t1 
Where scoreid > 0 LIMIT 3;

you get 

id  score
8   4
4   3
10  3

DBFiddle example https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=95e2051d560c2ac27fdcc8f9d04acf5d

Upvotes: 1

GMB
GMB

Reputation: 222412

I would approach this with a cumulative sum() (available in MySQL 8.0):

select 
    id, 
    score
from mytable
order by 
    sum(id = 6) over(order by score desc, id) desc, 
    score desc,
    id
limit 3

The sum() orders record in the required direction; as soon as the record that has id = 6 is met, the sum takes value 1. It allows to put these records on top. The rest is just adding the additional sorting criteria and limiting the number of results.

Demo on DB Fiddle:

| id  | score |
| --- | ----- |
| 6   | 2     |
| 9   | 2     |
| 1   | 1     |

In earlier versions of mysql, you can emulate the window sum with a user variable, as follows:

select 
    id, 
    score
from 
  (select @sm := 0) s
  cross join (select id, score from mytable order by score desc, id) t
order by 
    case when id = 6 then @sm := @sm + 1 end desc, 
    score desc,
    id
limit 3

Demo on DB Fiddle: same results

Upvotes: 2

Related Questions