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
1 5 2 50 3 25 4 30 5 52
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
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' );