我有以下数据:
id date mia 1 1/1/2017 3 1 1/2/2017 1 1 1/3/2017 2 2 1/4/2017 1 2 1/5/2017 4 2 1/6/2017 6 . . . .以此类推.
如果我输入为id=1，则应该获取2017-02-01的记录，如果输入id=2，则应获取2017-05-01的记录，即最高日期的前一个月的记录.
If I give input as id=1 I should fetch record of 2017-02-01 and if input id=2 then record of 2017-05-01 i.e record of previous month of the highest date.
推荐答案您可以使用:
SELECT * FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY id ORDER BY mia DESC) AS rn FROM table) sub WHERE rn = 2;