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;
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