SQl query to select minimum of one column maximum of another column-CodePudding

RID EID Amount   Date
-----------------------
1   1   10       2019-09-18
2   1   1        2019-09-20   
3   1   20       2018-04-01   
4   2   200      2018-04-01 
5   2   2        2019-06-08
6   3   2        2019-06-08
7   3   70       2019-06-08
8   3   2        2021-10-26

I want minimum date and maximum amount for EID like below

RID EID Amount   Date
-----------------------
3   1   20       2018-04-01   
4   2   200      2018-04-01 
7   3   70       2019-06-08

I achieved it with two inner joins in query but it may effect performance for large volumes of data. so need alternate

CodePudding user response：

It looks like a simple greatest-n-per-group problem. In MySQL 8 you can use ROW_NUMBER:

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY eid ORDER BY amount DESC, date ASC) AS rn
    FROM t
) AS x
WHERE rn = 1

CodePudding user response：

You code use following query

select *
from (Table)
orderby Amount desc
limit 3;

that will give you the greater 3 items in amount if you need more change the limit value.