Row Number to find Previous

[yunvme]

I have a database that stores customer information. what i need to do is show the customers previous rate. this is easy for customers that have a previous rate (by using row_number and selecting row_number 2)but not all customers have a previous rate so i need to select their current rate.
here is an example of the data:

CUST site serv rnk rate effective date
1 1 1 1 154.7 Jan 01, 2010
1 1 1 2 135.99 Jan 01, 2009
2 1 2 1 45.00 Jan 01, 2010

what i am trying to accomplish is:
CUST site serv rnk rate effective date
1 1 2 2 135.99 Jan 01, 2009
2 1 2 1 45.00 Jan 01, 2010

I have tried using row number to give each customer's rate a ranking:

SELECT CUST,site,serv,rate
row_number() OVER (partition by cust,site,serv order by effd desc) rnk
FROM cm19


i guess what i'm looking for is there a way to put in the where clause case when rnk>1 then rnk=2 else rnk=1?
or is there a better way to get the information that i'm looking for?

thanks in advance!

 

[bborissov]

-- Preparing test data, you do not need this
DECLARE @Cm19 TABLE (CUST int, site int,
                     serv int, rnk  int, 
                     rate  numeric(10,2),
                     effd datetime)
INSERT INTO @Cm19 VALUES (1, 1, 1, 1, 154.70, 'Jan 01, 2010')
INSERT INTO @Cm19 VALUES (1, 1, 1, 2, 135.99, 'Jan 01, 2009')
INSERT INTO @Cm19 VALUES (2, 1, 2, 1, 45.00,  'Jan 01, 2010')
---- End


SELECT Cm19.*
FROM (SELECT CUST,site,serv,rate,EffD,
             row_number() OVER (partition by cust,site,serv order by effd desc) Rnk,
             COUNT(1) OVER (partition by cust,site,serv) Cnt
       FROM @cm19) Cm19
WHERE Rnk = CASE WHEN Cnt > 1 THEN 2 ELSE 1 END

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.