• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

SQL2005 - Need to find nearest previous time in a table row to a given time

Hi, i have some data in a table representing historical prices. I need to find the data from a column "price" of the closest time prior to the given time where the product ID is equal to my product ID. I can get to this but it may not be the most efficient SQL query. Can someone recommend the most efficient SQL for this?


Data table
product_id         varchar(100)
price_datetime  varchar(16)  (format is "2009051915070100")
price                   float

I have the product_id in a variable @product_id and time_stamp in @price_time.  There are millions of rows in the data table, and prices may be within milliseconds of each other. If my @price_time value was "2009051912570875" I need to find the single row in the data table where the price_datetime value immediately precedes the price time given.

Thanks
Mike
0
mikegrad7
Asked:
mikegrad7
  • 2
1 Solution
 
Chris LuttrellSenior Database ArchitectCommented:
For this to be a fast query against a table with millions of rows, I assume you have indexes on product and price_datetime so this should work:
select *
from DataTable
where product_id = @product_id
and price_datetime = (
    select max(price_datetime)
    from DataTable
    where price_datetime < @price_time
    and product_id = @product_id)
0
 
mikegrad7Author Commented:
Thanks, that is what i was doing - is this the only or most efficient way? And yes, there are indexes on both product and time.
0
 
Chris LuttrellSenior Database ArchitectCommented:
If the table is indexed, this is the fastest way I have found to get this data.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now