Select one row based on Month and Year in sql

Question

I have a table like this:

 ID |     Cost     | Month |  Year  | InMonth | InYear |
--------------------------------------------------------
1081|     13000    |   5   |  2017  |    10   |  2016  |
1081|     13500    |   9   |  2016  |    10   |  2016  |
1081|     21000    |   2   |  2016  |    10   |  2016  |
1229|      6500    |   7   |  2017  |    10   |  2016  |
1229|      7800    |   5   |  2016  |    10   |  2016  |
1312|    110000    |   8   |  2017  |    10   |  2016  |
1312|    120000    |   5   |  2017  |    10   |  2016  |
1312|     99000    |   5   |  2016  |    10   |  2016  |

I want to show result data/row based on InMonth=Month and InYear=Year. If InMonth not same like Month and InYear not same like Year, get previous data/row. Like this:

 ID |     Cost     | Month |  Year  | InMonth | InYear |
1081|     13500    |   9   |  2016  |    10   |  2016  |
1229|      7800    |   5   |  2016  |    10   |  2016  |
1312|     99000    |   5   |  2016  |    10   |  2016  |

I have tried this:

select "ID", "Cost", "Month", "Year"
 from ( select "ID", "Cost", "Month", "Year",
  case when "InMonth">="Month" and "InYear">="Year"
   then row_number() over(partition by "ID" order by "Year" desc, "Month" desc)
  else 0
 end as RN
from price_history
) X
where RN<>0

SQL Fiddle: http://sqlfiddle.com/#!15/7b8b6f/1/0

Wouldn't it be better to move `"InMonth">="Month" and "InYear">="Year"` to where clause.. You won't even need `row_number` then — Gurwinder Singh, Oct 06 '17 at 09:40

score 1 · Answer 1 · answered Oct 06 '17 at 09:47

1

You are using Postgres. I would suggest distinct on:

select distinct on (id) t.*
from t
where year < inyear or
      (year = inyear and month <= inmonth)
order by id, year desc, month desc;

answered Oct 06 '17 at 09:47

Gordon Linoff

1,242,037
58
646
786

Thanks, I'll try in the next problem – Fadhel J Muhammad Oct 09 '17 at 02:17

score 1 · Accepted Answer · answered Nov 07 '17 at 03:40

Sorry, I just can post. I use this code:

select "ID", "Cost", "Month", "Year", "rn"
 from ( select "ID", "Cost", "Month", "Year",
  row_number() over(partition by "ID" order by "Year" desc, "Month" desc)
  as RN
from price_history where "InMonth">="Month" and "InYear">="Year" 
) X
where RN=1

Select one row based on Month and Year in sql

2 Answers2