CODEWITHSUNDEEP
sqloracle-database
ShB
ShB

Reputation: 319

Oracle get the last date for one month

I am not very good with Queries and Database. I have the the following data table 

Date        ID  Value
20160601    1   300
20160607    1   301
20160601    2   600
20160607    2   601
20160501    1   250
20160507    1   240
20160501    2   800
20160507    2   801

my requirement is to select the last date of a given month for each ID and show the value. for example, If I choose month 5 the result would be:

Date        ID  Value
20160507    1   240
20160507    2   801

and so on based on the month the user will enter.

I know it may look simple but I am really stuck and I would appreciate some help. Thanks.

Upvotes: 0

Views: 148

Answers (2)

Florin Ghita
Florin Ghita

Reputation: 17643

Find the maximum date then select all rows with that date.

select * 
from table
where date = (select max(date) from table where date like '201605%')

Upvotes: 0

sstan
sstan

Reputation: 36523

Assuming date is an actual date column (as it should be), you can use extract to compare the month value, and then the row_number() over ... analytic function to get the latest row per id value:

select date, id, value
  from (select date, id, value,
               row_number() over (partition by id order by date desc) as rn
          from tbl
         where extract(month from date) = 5)
 where rn = 1

Of course, I assume that your actual date column is called something else, as date is a reserved word.

Upvotes: 2

Related Questions