How to rank different fields in a query?

[YasserGIT]

I have a database that contains many number type fields.
I would like to compare the numbers in these fields within the same records and to rank them then put the rank number in new fields.

Thanks

[Luuk]

did you check previous answer on bytes.com ?
like this one: http://bytes.com/topic/access/answer...-rank-function

it explains gives all the info the get you going...

[YasserGIT]

That is different: I need the rank of the different fields within the same records (same row). Not within the same field (same column).

[Luuk]

Can you give a (maybe simplified) example of what you have, and what the situation should be after applying this rank?

[YasserGIT]

Fs are the fields
FsR are the rank fields as I need
F1 F2 F3 F4 F5 F1R F2R F3R F4R F5R
22 7 3 15 10 1 4 5 2 3

[Luuk]

Hmmm, i'm trying to solve this in MySQL, (i dont have/use access)

start with:

select * from rank_table;
+---+------+------+------+------+------+
| i | s1   | s2   | s3   | s4   | s5   |
+---+------+------+------+------+------+
| 1 |   22 |    7 |    3 |   15 |   10 |
| 2 | 4217 |  919 | 1186 |  128 |  200 |
| 3 | 2354 |  575 |  657 |   52 |   93 |
| 4 |   82 |  479 |  677 |   75 |  136 |
| 5 |   44 |  395 |  479 |   59 |   93 |
+---+------+------+------+------+------+

put values for every row in a column:

select i,s1 from rank_table 
	union 
	select i,s2 from rank_table 
	union 
	select i,s3 from rank_table 
	union 
	select i,s4 from rank_table 
	union 
	select i,s5 from rank_table

This will give you:

+---+------+
| i | s1   |
+---+------+
| 1 |   22 | = first row, first column
| 2 | 4217 |
| 3 | 2354 | = third row, first column
| 4 |   82 |
| 5 |   44 |
| 1 |    7 | = second row, second column
| 2 |  919 |
| 3 |  575 |
| 4 |  479 |
| 5 |  395 |
| 1 |    3 |
| 2 | 1186 |
| 3 |  657 |
| 4 |  677 | = fourth row, third column 
....

add the rownumber. I'm not sure how to do this in access, but MySQL has a little trick to do it:

set @a:=0;
select @a:=CASE WHEN t.i=1 THEN @a+1 ELSE @a END a,t.i,t.s1 from 
	(select i,s1 from rank_table 
	union 
	select i,s2 from rank_table 
	union 
	select i,s3 from rank_table 
	union 
	select i,s4 from rank_table 
	union 
	select i,s5 from rank_table ) t ;

output:

+------+---+------+
| a    | i | s1   |
+------+---+------+
|    1 | 1 |   22 |
|    1 | 2 | 4217 |
|    1 | 3 | 2354 |
|    1 | 4 |   82 |
|    1 | 5 |   44 |
|    2 | 1 |    7 |
|    2 | 2 |  919 |
|    2 | 3 |  575 |
|    2 | 4 |  479 |
|    2 | 5 |  395 |
|    3 | 1 |    3 |
|    3 | 2 | 1186 |
|    3 | 3 |  657 |
|    3 | 4 |  677 |
......

Last step, sort by second column, and descending on third:

set @a:=0;
select a,i,s1 from (
	select @a:=CASE WHEN t.i=1 THEN @a+1 ELSE @a END a,t.i,t.s1 from 
		(select i,s1 from rank_table 
		union 
		select i,s2 from rank_table 
		union 
		select i,s3 from rank_table 
		union 
		select i,s4 from rank_table 
		union 
		select i,s5 from rank_table ) t ) u
order by i, s1 desc;

results in:

+------+---+------+
| a    | i | s1   |
+------+---+------+
|    1 | 1 |   22 |
|    4 | 1 |   15 |
|    5 | 1 |   10 |
|    2 | 1 |    7 |
|    3 | 1 |    3 |
|    1 | 2 | 4217 |
|    3 | 2 | 1186 |
|    2 | 2 |  919 |
|    5 | 2 |  200 |
|    4 | 2 |  128 |
|    1 | 3 | 2354 |
|    3 | 3 |  657 |
|    2 | 3 |  575 |
....

In column 'a' you will see your ranking... ;)

The simple answer is: YES it can be done, but if you store your numbers not in one row, but in more rows than it would be a lot easier to get this result.

[YasserGIT]

That is good.
However, I need the rank fields beside the original fields not on top of each others
Thanks a lot

[NeoPa]

I suspect the first thing you need to understand is about Database Normalisation and Table Structures. What you ask is confusing for database experts as they would never think along those lines. All I can suggest is that you do multiple comparisons of the data to determine which order they're in. A messy solution certainly, but it rather matches the question I'm afraid.

Even if you do decide to continue along this course and manage to get it working, your database will be a long way short of Normalised, which ought to worry you.

[YasserGIT]

Can we use combination of Switch, Max and Min functions to do it

[NeoPa]

Switch() may well be worth proceeding with. Min() and Max() are functions which aggregate across records, not across fields (So No. They will be entirely useless in this scenario - at least until you get a sensible design and hold the data across records instead of fields of course).

[YasserGIT]

I am interrested in the data across fields because my database contaiains now thousands of records and about 30 fields. If I reversed it the fields will be in thousands and the records only 30.

[NeoPa]

YasserGIT:
If I reversed it the fields will be in thousands and the records only 30.

Clearly that would be quite a ridiculous approach, but who mentioned anything about reversing it?

[Rabbit]

The way your data is, you have to use Luuk's approach.

1) Unpivot your data to normalize it.
2) Use a ranking query.
3) Repivot if you want to denormalize your data.

Acess doesn't have a row number so you won't be able to use Luuk's method of ranking. But you can achieve the same resutls using either a subquery in the select or by joining the source to itself.

[YasserGIT]

Excuse me, for being not very good in access.
I know that there is no built-in functions in access to compare values in records (it compares values in fields only).
I wonder if it is possible to invent a new function to do that.
I did a search and I found a function that compare numbers in records to turn the maximum and minimum.
Can any one do the same for ranking the numbers in records.

The function was like that:

Function Maximum(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Variant
 
' Set the variable currentVal equal to the array of values.
currentVal = FieldArray(0)
 
' Cycle through each value from the row to find the largest.
For I = 0 To UBound(FieldArray)
If FieldArray(I) > currentVal Then
currentVal = FieldArray(I)
End If
Next I
 
' Return the maximum value found.
Maximum = currentVal
 
End Function
Function Minimum(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Variant
 
' Set the variable currentVal equal to the array of values.
currentVal = FieldArray(0)
 
' Cycle through each value from the row to find the smallest.
For I = 0 To UBound(FieldArray)
If FieldArray(I) < currentVal Then
currentVal = FieldArray(I)
End If
Next I
 
' Return the minimum value found.
Minimum = currentVal

[Rabbit]

Please use code tags when posting code.

Yes, you could use code. It would take longer to run. But it's up to you which approach you use, SQL or VBA.