Ranking fields - 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

General SQL Server Forums

New to SQL Server Programming

Ranking fields

Author

Topic

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-01-29 : 08:00:34

Hi

I have six fields in a table f1..f6 each field will contain the values 'H1', 'x' 'xm' or 'us'.
I need to rank them in the order above only outputting the ones that contain those values (there may be other values in those fields but I only want to output where those above exist).

So the output I need is going to look like:-
f1 - f2 - f3 - f4 - f5 - f6
H1 - H1 - H1 - x - x - H1
H1 - x - H1 - xm - x - us
H1 - x - xm - xm - us
x - x - us - - us
xm - - us - - us
xm - - - - us
us - - - - us

Showing the value for each field in the ranked order 'H1', 'x' 'xm' or 'us'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-29 : 08:05:19

[code]ORDER BY CASE f1
WHEN 'H1' THEN 0
WHEN 'x' THEN 1
WHEN 'xm' THEN 2
ELSE 3
END,
CASE f2
WHEN 'H1' THEN 0
WHEN 'x' THEN 1
WHEN 'xm' THEN 2
ELSE 3
END,
CASE f3
WHEN 'H1' THEN 0
WHEN 'x' THEN 1
WHEN 'xm' THEN 2
ELSE 3
END,
CASE f4
WHEN 'H1' THEN 0
WHEN 'x' THEN 1
WHEN 'xm' THEN 2
ELSE 3
END,
CASE f5
WHEN 'H1' THEN 0
WHEN 'x' THEN 1
WHEN 'xm' THEN 2
ELSE 3
END,
CASE f6
WHEN 'H1' THEN 0
WHEN 'x' THEN 1
WHEN 'xm' THEN 2
ELSE 3
END[/code]

E 12°55'05.63"
N 56°04'39.26"

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-01-29 : 08:43:07

Thanks (again)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources