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