select value by oldest date within a range
802443Oct 6 2010 — edited Oct 6 2010Hi All,
I hope I can explain this clearly...
Example data:
row, col1, col2, col3, date_col, price_amt
0, A, A, C, 01-DEC-2009, 2.10
1, A, A, C, 01-JAN-2010, 2.00
2, A, A, C, 10-JAN-2010, 1.98
3, A, A, C, 01-FEB-2010, 1.66
4, A, B, C, 10-JAN-2010, 2.00
5, A, B, C, 11-JAN-2010, 1.98
6, A, B, C, 01-FEB-2010, 1.66
I need a query to give me the record with the oldest value in a range. Given the data above for the range Jan 2010 - Feb 2010, I need the price for the oldest date value and non of the other matching rows. In the example above rows 1 and 4 would be returned.
SELECT col1, col2, col3, oldest price_amt in range
FROM table_name
WHERE date_col BETWEEN '01-JAN-2010' AND '28-FEB-2010'
GROUP BY col1, col2, col3, price_amt
Any suggestions would just be awesome - this is breaking my brain.
Thomas