Select Most Recent Rows Per Month

[DarthEvader]

I have a table where each row contains a single day's pricing information. The table has one row per "price date", going back to May of 2002.

Example:
UNIQUE_ID, TICKER, NAME, PRICE_DATE, PRICE
A1234, TICKER1, Company 1, 5/5/2007, 12.5523423
A1235, TICKER2, Company 2, 5/31/2007, 1423.2342


I am trying to figure a way to select the row of the final "price_date" of every month (64 total months). The problem is that the final price_date is not always on the last day of the month. So for example, for June 2007 I would want the price from Friday June 29, not Saturday June 30.

So I need something that will act like the max(price_date) function, except return each row that has the maximum date in each month. Can anyone think of a good way to do this?

I am using Sybase v.12.5.3

 

[gmmastros]

This would work with Microsoft SQL Server, and since the SQL is (in most cases) similar...

Select *
From   TableName
       Inner Join (
         Select UNIQUE_ID, Max(PRICE_DATE) AS Price_Date
         From   TableName
         Group By UNIQUE_ID, Month(PRICE_DATE)
         ) As A
         On TableName.UNIQUE_ID = A.UNIQUE_ID
         And TableName.PRICE_DATE = A.PRICE_DATE

If Month is not a recognized function, then you may be able to accomplish the same thing with DatePart(Month, PRICE_DATE)

Please let me know if this works for you.

-George

"the screen with the little boxes in the window." - Moron

 

[DarthEvader]

Hi George, I wasn't able to get it to work quite right, however I was given another solution that worked perfectly:

select year(pricedate)
, month(pricedate)
, pricedate
, price
from daTable as T
where pricedate =
( select max(pricedate)
from daTable
where year(pricedate) = year(T.pricedate)
and month(pricedate) = month(T.pricedate) )