January 6, 2003 at 2:41 pm
I have a table with week, store, price. There are many records for each store with varying prices.
Table
Week store price
1 183 .95
4 183 .99
9 183 1.09
13 183 .99
17 183 .00
How can I write a statement to return the most recent non zero price? So I would want week 13's price from the sample data above.
January 6, 2003 at 3:12 pm
This might work for you:
create table #t (week int, store int, price money)
insert into #t values (1 , 183, .95)
insert into #t values (4 , 183, .99)
insert into #t values (9 , 183, 1.09)
insert into #t values (13 , 183, .99)
insert into #t values (17 , 183, .00)
insert into #t values (4 , 182, .99)
insert into #t values (9 , 182, 1.09)
insert into #t values (13 , 182, 1.99)
insert into #t values (17 , 182, .00)
select t.week, t.store, t.price from #t t
where
t.week = (select top 1 week from #t where price > 0 and t.store = store
order by week desc)
and
t.store = (select top 1 store from #t where price > 0 and t.store = store
order by week desc)
drop table #t
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
January 6, 2003 at 3:42 pm
Great exactly what I was looking for thanks a bunch!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply