CODEWITHSUNDEEP
sqlsqlitegreatest-n-per-group
Rishi Chandak
Rishi Chandak

Reputation: 31

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

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

Thank You

Upvotes: 1

Views: 283

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

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'
                  );

Upvotes: 1

Richard Xu
Richard Xu

Reputation: 223

select underHeadId, max(price) from t where wefDate <= "2016-11-20" group by underHead;

Upvotes: 0

Related Questions