Join with highest rank - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Join with highest rank

Author

Topic

JasonC
Starting Member

4 Posts

Posted - 2002-06-14 : 17:50:39

Given a statement like so :


select mt.rank as repair,mb.rank as problem,reportid,repairdesc,problemdesc
FROM reports AS ft 
FULL JOIN CONTAINSTABLE(reports,repairdesc,'"full" or "center"') AS mt ON ft.reportid = mt.[KEY]
JOIN CONTAINSTABLE(reports,problemdesc,'"full" or "center"') AS mb ON ft.reportid = mb.[KEY]
order by repair desc,problem desc

I wish to order the returned records by the highest rank of repair or problem
Currently the order is as above, which can return :


144	128
144	128
NULL	192
NULL	128
NULL	128
NULL	128

Id like it to return :


NULL	192
144	128
144	128
NULL	128
NULL	128
NULL	128

Then I can take an average of columns with 2 values.
So, I guess the question is, how can I average 2 columns when one is potentially null and would give null answer?


....((mt.rank + mb.rank)/2)...

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-06-14 : 18:58:07

quote:
So, I guess the question is, how can I average 2 columns when one is potentially null and would give null answer?

Use the IsNull() function to change null values to 0 or some other value that you can average.

For example:
....((isnull(mt.rank,0) + isnull(mb.rank,0))/2)...

JasonC
Starting Member

4 Posts

Posted - 2002-06-14 : 19:58:59

Looks good thanks!

To allow for averaging, this is what I want to arrive at (I put more weight on column 2) :
eg


mt.rank|mb.rank
NULL	192  = (192 + 192)/2
144	128  = (144 + 128)/2
144	128  = (144 + 128)/2
NULL	128  = (128 + 128)/2

using:
(isnull(mt.rank,isNull(mb.rank,0)) + isnull(mb.rank,isNull(mt.rank,0)))/2 as rank

So, if mt.rank is null, then apply the value mb.rank so the sum is equal.

Question
Is the following isnull needed?
(isnull(mt.rank,isNull(mb.rank,0))

Can a null be returned in a CONTAINSTABLE as a rank? or am I doing an error check that isnt necessary as a null cant be returned (in both rank joined columns)

tia

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources