MySQL SORT BY starting from specific result position?

Issue

I have a set of data within a database as follows:

id | name    | price | balance
-------------------------------
1  | john    | 100   | 40
2  | dave    | 50    | 29
3  | steven  | 29    | 200
4  | mike    | 482   | 19
5  | clare   | 21    | 102

I want to grab the results from this database and ORDER BY balance in descending order. (highest balance first) – though I want the result set to start at the X position, for this sake lets say we want the results after the third position, so the first two results should not show.

This should be the result of the query:

id | name    | price | balance
-------------------------------
1  | john    | 100   | 40
2  | dave    | 50    | 29
4  | mike    | 482   | 19

The query I have so far is:

SELECT * FROM `my_table` ORDER BY `balance` DESC

However this returns all results, and now I have to manually filter the first two out.

Solution

ROW_NUMBER is helpful here, if you are using MySQL version 8+:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY balance DESC) rn
    FROM yourTable
)

SELECT id, name, price, balance
FROM cte
WHERE rn >= 3
ORDER BY balance DESC;

enter image description here

For earlier versions of MySQL, we can try simulating row number using user variables:

SELECT id, name, price, balance
FROM
(
    SELECT (@row_number:=@row_number + 1) AS rn, t.*
    FROM yourTable t, (SELECT @row_number := 0) r
    ORDER BY balance DESC
) t
WHERE rn >= 3
ORDER BY balance DESC;

Demo

Answered By – Tim Biegeleisen

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published