How do I select single row in a group based upon date?
We have an item price file that consists of It开发者_StackOverflow社区em number, Price Effective Date, and Price. We are going to have a price increase at the first of the year. What I want to do is add the price records now which would have a later effective date, and select the current price based upon the current date. I.E. Current date is 08/03/11, I want to pull the ITEM1 01/01/09 record:
1. ITEM1 01/01/01 100.00
2. ITEM1 01/01/05 150.00
3. ITEM1 01/01/09 200.00
4. ITEM1 01/01/12 250.00
Once the first of the year hits - 01/01/12, I would select the 01/01/12 record.
You can't use MIN or MAX function as it would pull the first or last record, not the correct record.
Any ideas are greatly appreciated.
Try this:
select * from tb
join (
select tb.item,
MAX(tb.effDate) effDate
from tb
where tb.effDate <= currentDate
group by tb.item
) tmp
on tb.item = tmp.item
and tb.effDate = tmp.effDate
Subquery will identify the correct row, and the main query will fetch the row again to get the price.
SELECT * FROM Table WHERE Date >= '01/01/09'
If you are using VB.NET, this can be very easy. It looks like your fields are seperated by a single ' ' (space). This will give you something like this:
Dim Record As String = "ITEM1 01/01/01 100.00 ITEM1 01/01/05 150.00 ITEM1 01/01/09 200.00 ITEM1 01/01/12 250.00"
Dim Fields() As String = Record.Split(" ")
Dim LastItemNo As String = Fields(Ubound(Fields) - 2)
Dim LastDate As Date = Date.Parse(Fields(Ubound(Fields) - ))
Dim LastPrice As Single = Val(Fields(Ubound(Fields)))
Debug.WriteLine("Item: " & LastItemNo & "; Date: " & Format(LastDate, "MM-dd-yyyy") & "; $" & Math.Round(LastPrice, 2))
In this case, it will output: Item: Item1; Date: 01/01/2012; $250.00
I hope this is what you were looking for.
If its just SQL you need try
SELECT [columname price] FROM [tablename] WHERE EXTRACT(YEAR FROM CURDATE()) = EXTRACT(YEAR FROM [columname date] ) limit 1
replace [columname]
with the name of the colum witch holds the data
replace [tablename]
with the name of the table witch holds the data
I believe that should do the trick.
You order by date, in descending order (so that the latest record is "on top") and use WHERE
to filter out dates which are after the date youre instrested. The LIMIT clause (in case of mySQL) makes the resultset to have only one row. So query would be something like
SELECT *
FROM tab
WHERE(Number = ITEM1)AND(Date <= CURRENT_DATE)
ORDER BY Date DESC
FETCH FIRST 1 ROW ONLY
Try this:
SELECT item_number, MAX(price_effective_date)
FROM items_table
WHERE price_effective_date < current_date
GROUP BY item_number
精彩评论