Clever Geek Handbook

Actual price on this date

got a table with dates and prices.

Date Price
2012-01-01 25
2012-01-05 12
2012-01-10 10

Is there some function that allows me to find the current price where on '2012-01-07'? Without me, knowing about other dates.

Pseudoquery: select a price where currentprice ('2012-01-07')

Thank!

+3


source to share


3 answers


MySQL:

select price from your_table 
where date <= '2012-01-07'
order by date desc
limit 1


SQL Server:

select top 1 price from your_table 
where date <= '2012-01-07'
order by date desc
+3


source


If you don't have the use of ROW_NUMBER () and want a general solution, then you need to join a subquery.

Get the date you want, then get the data for that date.



SELECT
  *
FROM
  yourTable
INNER JOIN
(
  SELECT MAX(yourDate) AS maxDate FROM yourTable WHERE yourDate <= @dateParameter
)
  AS lookup
    ON yourTable.yourDate = lookup.maxDate
+1


source 


 select price 
 from table1 t 
 where t.date = ( select max(t2.date) 
                  from table1 t2
                  where t2.date <= '2012-01-07' )

Please note that this is not a copy and paste answer as we do not know what the data type for the column is date

.

+1


source






More articles:


All Articles