0

I have the following data:

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

.
.
.
.

and so on.

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.

user1643763
  • 15
  • 4

2 Answers2

2

You could use:

SELECT *
FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY id ORDER BY mia DESC) AS rn
      FROM table) sub
WHERE rn = 2;
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
0

you may not define a column named as date, instead i use date_. For former versions you may refer to @lad2025 's answer, if you're on oracle12c, you may query with the following :

select min(date_) min_date 
  from
(
 select date_
   from mytable
  where id = &i_id
  group by id, date_
  order by date_
  fetch first 2 rows only
 );
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55