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.

I am trying to figure a way to select the row of the final "price date" of every month. The problem is that the final price 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.

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

 

[fredericofonseca]

hum.. The following even if it doesn't work, should give you an idea.

select [price date], price
from my_tbl
where [price date] in
(select daymon||max_day
from
(
select year[price date]||month[price date] as daymon, max(day[price date] as max_day
from my_tbl
group by year[price date]||month[price date]
)
)

Not sure which DB you are using, and neither sure if ANSI SQL allows the group by to contain an expression.

A alternative to the above


select [price date], price
from my_tbl
where [price date] in
(select daymon||max_day
from
(daymon, max(day[price date] as max_day
select year([price date])||month([price date]) as daymon, day([price date]) as day_1
from my_tbl
) z
group by daymon
)
)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[DarthEvader]

Thanks for responding to my question. Here are some more details:

-I am using Sybase v.12.5.3
-Fields in table: UNIQUE_ID, TICKER, NAME, PRICE_DATE, PRICE

Should I post this question in the Sybase forum? I thought that the standard ANSI SQL functions may cover this issue, but perhaps not because I don't have access to a daymon function.

 

[fredericofonseca]

post on the sybase forum.

But I am not using any daymon function. daymon is a alias I am giving to the concatenation of the year and month part of the date. (wrong name perhaps. should be yearmonth)

only functions on the above sql are
year
month
day

which all DB's should have, and that are also ANSI sql.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[r937]

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) )

r937.com | rudy.ca

 

[DarthEvader]

r937, your solution was awesome! It worked perfectly. Thanks so much.