I won't say much nonsense, let's just read the code~
select * from (select ROW_NUMBER () OVER (ORDER BY fat desc nulls last) AS xuhao,foodnum,foodname,fat from ek_food where isdel=0) food where foodnum = 'Ss192008'
Issues involved
1. When sorting, the field value of null will be ranked first, resulting in inaccurate data. Solution: Add nulls last after order by
2. Add a sequence number to the query result select ROW_NUMBER () OVER (ORDER BY fat desc nulls last) AS xuhao
Supplement: Use PostgreSQL to rank data
1. Requirements: Ranking and calculating the following user points
| user_id | name | score |
| 1 | john | 1000 |
| 2 | mike | 1200 |
| 3 | jelly | 1300 |
| 4 | * | 1500 |
| 5 | nanny | 1200 |
You need to know the corresponding points ranking of users with user_id = k
2. Implementation through PostgreSQL's window function
SELECT user_id, name, score, RANK() OVER (ORDER BY score DESC) FROM user;
| user_id | name | score | rank |
| 4 | * | 1500 | 1 |
| 3 | jelly | 1200 | 2 |
| 2 | mike | 1300 | 3 |
| 5 | nanny | 1500 | 3 |
| 1 | john | 1200 | 5 |
To obtain users with ranking < 3:
SELECT user_id, name, score, user_rank FROM (SELECT user_id, name, score, RANK() OVER (ORDER BY score DESC) AS user_rank FROM user) AS T WHERE user_rank < 3;
-- Note that subquery needs to write alias in from
| user_id | name | score | rank |
| 4 | * | 1500 | 1 |
| 3 | jelly | 1200 | 2 |
The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.