I'm trying to create a query which will return the current dvd price and the previous dvd price from between date values.
The prices uses a date checked column. Below is the structure of my prices table:

My current query isn't working correctly.

This is returning more values because is being looped in the select.

What I'd like returning is:


Any advice would gratefully appreciated.

Thanks,
Lee

use any of these and check r u getting ur requirement r not
SELECT top 2 price_id,store_id,disc_id,price_total,price_checke d FROM prices order by price_checked desc

declare @stdate datetime ,@enddate datetime
select @stdate = '2009-2-3' , @enddate = '2009-2-5'

SELECT price_id,store_id,disc_id,price_total,price_checke d FROM prices
WHERE price_checked BETWEEN @stdate AND @enddate
order by price_checked desc

The following assumes the price_id's always go up by one and that there are no missing id's. I've used a PHP variable for the disk id. You mentioned you wanted the price from between date values but your SQL was puling the latest price and the price before it - I guessed you wanted the latest prices :

Code:

select p1.store_id,p1.disc_id,p1.price_total,p1.price_checked 
from   prices p1
where  p1.disc_id = $disk_id
       and p1.price_id >= (
             select max( p2.price_id ) - 1
             from   prices p2
             where  p2.disc_id = p1.disk_id )

If you did want the results for between two dates then I'd do it in a way similar to bklr's where the dates are PHP variables :

Code:

select p1.store_id,p1.disc_id,p1.price_total,p1.price_checked 
from   prices p1
where  p1.disc_id = $disk_id
       and p1.price_checked between $start_date and $end_date
order by p1.price_checked desc
limit 2

Won't this code just produce all the prices between these dates? The variable syntax suggests Sybase / MS SQL rather than MySQL. Adding a rowcount would fix the number of dates returned

This query worked great but i forgot that when there is another store (store_id) it will only select 1 store, i'm looking at selecting multiply stores and select the highest price from that store between the dates.


Lee

I think you need to supply a few examples.

My price table looks like this:
I would like the query to return the total price between the newest date but there could be many stores in the price table;

would like it like the example below:


Many thanks,
Lee

Without creating your tables and inserting all your data it's difficult to test on my side - something like this might do it but you can quickly find out :

That query worked but only when the store_id where next to together like 1,1,2,2,3,3,4,4
If the store_id went 1,2,3,4,1,2,3,4 the query would only return one row.

Thanks,
Lee

Using your original data it produced exactly the results you wanted - as I understand it!

I believe the query will return 4 rows but whether these are the rows you want or not is difficult for me to guess