A query to return rows in a date range, but returns only the maximum column value

Question

What I want to do is select a price from a table where the indicated date is between the start and end dates in the table.

SELECT price FROM table WHERE 'dategiven' BETWEEN startdate AND enddate 

It is quite simple with a datetime filter. The problem is that I have several entries in the time window, I have a version column.

Below is an example of my table:

I want my query output to be as below, where my dategiven is 2013-08-25

Milk has 3 entries, 2 of them are valid for dategiven (2013-08-25). Then I want to return the result with the highest version?

sort of:

 SELECT price FROM table WHERE 'dategiven' BETWEEN startdate AND enddate AND max(version) 

Answer 1

Using row_number() to row_number() Rows

 select product, price, version from ( select *, row_number() over (partition by product order by version desc) rn from yourtable where @dategiven between startdate and enddate ) v where rn = 1 

Answer 2

It may not be the most effective way, but:

 select price from table where 'dategiven' between startdate and enddate and version = ( select max(version) from table t2 where t2.product = table.product and 'dategiven' between startdate and enddate ) 

Answer 3

You can also try this.

 select t.product, t.price, version from table t inner join (select product, max(version) maxVerion from table where 'dategiven' between startdate and enddate group by product) temp on temp.product = t.product and t.version = temp.maxVerion 

Hope this helps.

Answer 4

My computer does not have sqlservert installed, but mysql. I do not know what is difficult.

  select t.product, t.price, v.version ( select temp.product, max(temp.version) as version from ( select product, price, startDate, endDate, version from Table where 'dategiven' between startDate and endDate ) as temp group by temp.product ) as v inner join Table as t on v.version = t.version