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.

a_horse_with_no_name
  • 497,550
  • 91
  • 775
  • 843
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
  • 139,860
  • 19
  • 198
  • 228
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
  • 47,993
  • 9
  • 26
  • 51