How to control row number priority?

[makeitwork09]

I am using Microsoft SQL Server 2005

I am trying to create a ranking where brief has priority over actual which has priorty over priority. These are user defined types.

In addition, it should be the most recent statement date that is extracted for the corresponding user defined type.

The way I have the code below however, if a property has all three of the user defined types and if proforma is the most recent user defined type, it is getting a row number of 1, when I want it to be 3, even though it has the most recent date. The only time proforma should be the row number of 1 is if it is the only user defined type for the property.

Therefore I want

prop usertype statement_Date
empire1 brief 2011-02-13
empire1 actual 2010-12-30
empire1 proforma 2012-05-15
gmbuild actual 2010-12-30
gmbuild proforma 2012-05-15
cardnil proforma 2011-03-20

Any thoughts?

select o.prop_code, o.user_def_type, 
ROW_NUMBER() OVER (partition by o.prop_code ORDER BY 
		   o.statement_date desc,
		   case when o.user_def_type = 'brief' then 1
			when o.user_def_type = 'actual' then 2 
			when o.user_def_type = 'proforma' then 3
			else 4
                   end) as [rownum],
							   o.statement_date, m.max_statement_date,o.debt_serv_covrg,o.net_income,
o.ann_debt_serv,o.gross_income,o.total_expenses
from opstatmt o
left outer join (select x.prop_code,max(x.statement_date) as[max_statement_date]
		 from opstatmt x
		 group by x.prop_code) as m on
m.prop_code = o.prop_codewhere 
where o.user_def_type in ('brief','actual','proforma')

Thanks

 

[markros]

Switch the order of your ORDER by statements, e.g. first do by user-defined type case statement and then by date DESC.

PluralSight Learning Library

 

[makeitwork09]

I have tried that, it does not work.

I provided an overly simplified example. It is possible within each propety to have more than one of the same type of user defined type. The only difference is the statement date.

For example, I would not get the following results if I simply changed the order by.

prop    usertype   statement_Date
empire1 brief      2011-02-13
empire1 actual     2010-12-30
empire1 proforma   2012-05-15
empire1 brief      2007-08-10
gmbuild actual     2010-12-30
gmbuild proforma   2012-05-15
gmbuild actual   2009-12-30
cardnil proforma   2011-03-20

 

[markros]

Can you post some samples of what you have an input and desired output. If the type is more important than date, then type should be first in the ORDER by and vs. versa. I think you're making this problem harder than it is, but without seeing input and output based on input I can not really tell.

PluralSight Learning Library

 

[makeitwork09]

markros,

Here is what I want:

prop    usertype   statement_Date rownum
empire1 brief      2011-02-13 1
empire1 actual     2010-12-30 2
empire1 proforma   2012-05-15 3
empire1 brief      2007-08-10 4
gmbuild actual     2010-12-30 1
gmbuild proforma   2012-05-15 2
gmbuild actual   2009-12-30 3
cardnil proforma   2011-03-20 1

I will eventually select where rownum = 1

 

[markros]

So, the suggested change is going to work. I don't see why not. The RowNum will be different, but the RowNum = 1 will be the same if you order by case, Date DESC.

PluralSight Learning Library

 

[makeitwork09]

I thought it would work too, but it wasn't. I'm not at the office any longer. I will check again in the morning, but I know I tried that and was puzzled as to why it was not working.

 

[markros]

If you have a chance to post a reproducible script which I can run to test it, it will be great.

PluralSight Learning Library

 

[makeitwork09]

Thanks markros.

After a break from it, and some sleep, I was able to get it to work. I had to make some slight modifications to the code I originally posted to deal with unexpected scenarios.

Thanks