Query - returning the name of the field with the greatest value

[Gilad Weil]

Hi,

I have a table with 12 numeric fields. In every row, I need a new field containing the field name with the largest value in the row.
I attached screenshot, the relevant fields are az1-12.



Thanks!

Gilad

[ranman256]

Build a 'report' table that looks just like the one above.
then make 12 append queries, each one gets the MAX of 1 column, append it to the report table.
after you append all 12, sum them. The zeros will fall out.

[Gilad Weil]

Thanks. If I understood correctly,
The result will be 12 rows with the maximum value of each column.
But what I need is for every row (2746293 rows), in a new column, the field name of the column containing the max value.

Gilad

[hkrobster]

First: What do you want to happen if more than one field have the co-maximum value?

[Gilad Weil]

Get the first

[John_G]

I don't know of any way to do that without using a VBA function which would scan the 12 fields to determine the name of the one with the largest value. If your data in each row does not change after it has been entered, this is one case where you might want to put that extra field into the table. Normally, putting calculated fields in a table is frowned on, but putting that function into a query would cause the query to run *very* slowly with that many rows.

[hkrobster]

Note that you should not use "all" as a table name, I have used "alll"

SELECT Switch(


az1>az2 And az1>az3 And az1>az4 And az1>az5 And az1>az6 And az1>az7 And az1>az8 And az1>az9 And az1>az10 And az1>az11 And az1>az12,"az1",


az2>az1 And az2>az3 And az2>az4 And az2>az5 And az2>az6 And az2>az7 And az2>az8 And az2>az9 And az2>az10 And az2>az11 And az2>az12,"az2",


az3>az1 And az3>az2 And az3>az4 And az3>az5 And az3>az6 And az3>az7 And az3>az8 And az3>az9 And az3>az10 And az3>az11 And az3>az12,"az3",


az4>az1 And az4>az2 And az4>az3 And az4>az5 And az4>az6 And az4>az7 And az4>az8 And az4>az9 And az4>az10 And az4>az11 And az4>az12,"az4",


az5>az1 And az5>az2 And az5>az3 And az5>az4 And az5>az6 And az5>az7 And az5>az8 And az5>az9 And az5>az10 And az5>az11 And az5>az12,"az5",


az6>az1 And az6>az2 And az6>az3 And az6>az4 And az6>az5 And az6>az7 And az6>az8 And az6>az9 And az6>az10 And az6>az11 And az6>az12,"az6",


az7>az1 And az7>az2 And az7>az3 And az7>az4 And az7>az5 And az7>az6 And az7>az8 And az7>az9 And az7>az10 And az7>az11 And az7>az12,"az7",


az8>az1 And az8>az2 And az8>az3 And az8>az4 And az8>az5 And az8>az6 And az8>az7 And az8>az9 And az8>az10 And az8>az11 And az8>az12,"az8",


az9>az1 And az9>az2 And az9>az3 And az9>az4 And az9>az5 And az9>az6 And az9>az7 And az9>az8 And az9>az10 And az9>az11 And az9>az12,"az9",


az10>az1 And az10>az2 And az10>az3 And az10>az4 And az10>az5 And az10>az6 And az10>az7 And az10>az8 And az10>az9 And az10>az11 And az10>az12,"az10",


az11>az1 And az11>az2 And az11>az3 And az11>az4 And az11>az5 And az11>az6 And az11>az7 And az11>az8 And az11>az9 And az11>az10 And az11>az12,"az11",


az12>az1 And az12>az2 And az12>az3 And az12>az4 And az12>az5 And az12>az6 And az12>az7 And az12>az8 And az12>az9 And az12>az10 And az12>az11,"az12",


) AS Top_column
FROM alll
;



It's pretty ugly but it works

[John_G]

I think your "ugly" code will fail unless the ">" are changed to ">=". For example, if AZ1 is the largest, but there is another field with the same value, the first expression will have a value of "False".

[hkrobster]

Ah yes, true enough. There probably are a few ways to improve it..

[June7]

Alternative is to normalize data structure then use a TOP N nested query. Review http://allenbrowne.com/subquery-01.html#TopN

Your 12 AZ_ tables could probably be 1 table.

A UNION query can arrange the data into a normalized structure then use that query same as a table in subsequent queries.