Get the latest price SQLITE

I have a table which contain _id, underSubheadId, wefDate, price. Whenever a product is created or price is edited an entry is made in this table also. What I want is if I enter a date, I get the latest price of all distinct UnderSubheadIds before the date (or on that date if no entry found)

_id underHeadId wefDate   price
1      1      2016-11-01    5 
2      2      2016-11-01    50
3      1      2016-11-25    500
4      3      2016-11-01    20 
5      4      2016-11-11    30
6      5      2016-11-01    40
7      3      2016-11-20    25
8      5      2016-11-15    52

If I enter 2016-11-20 as date I should get

I have achieved the result using ROW NUMBER function in SQL SERVER, but I want this result in Sqlite which don't have such function. Also if a date like 2016-10-25(which have no entries) is entered I want the price of the date which is first. Like for 1 we will get price as 5 as the nearest and the 1st entry is 2016-11-01.

This is the query for SQL SERVER which is working fine. But I want it for Sqlite which don't have ROW_NUMBER function.

select underSubHeadId,price from(
    select underSubHeadId,price, ROW_NUMBER() OVER (Partition By underSubHeadId order by wefDate desc) rn from rates
    where wefDate<='2016-11-19') newTable
where newTable.rn=1

Thank You

Solution

This is a little tricky, but here is one way:

select t.*
from t
where t.wefDate = (select max(t2.wefDate)
                   from t t2
                   where t2.underSubHeadId = t.underSubHeadId and
                         t2.wefdate <= '2016-11-20'
                  );