SQL Query: Search Row

[cation555]

Hi,

I have a db table that looks like this:

http://img109.imageshack.us/img109/7503/db0vk.png

My question is how can I query a row (for instance, what if I want to find the highest value in Min. Val.)

Thanks,

 

[Argo]

Please rephrase (or give better example). I don't understand what you need.

 

[cation555]

Edited original post for clarity.

 

[Jeraden]

I still don't understand what you need.

You mean on the 2nd row which has field3 = 'Min. Val.', you want to find the highest value from fields 5 through 11, just for that particular row?

 

[kamper]

Assuming I understand correctly, I'd advise starting by redesigning your data layout, or not doing it in sql (do it in your program code).

 

[KB]

Your database violates normal forms 2 and 3. Take a look at database normalization for a better explanation.

I may not understand your problem well but this is how I would do the database:
I would set a table for dates
TimeID------Date
1--------------Oct 1 1948
2--------------Dec 1948

then a table for values linked to the dates
ValueID----TimeFK-------Value
1-------------1--------------12.3
2-------------1--------------2.4
.......

Then you can query max value using SELECT MAX(Value) FROM ValueTable WHERE TimeFK = 1

 

[cation555]

Originally posted by: Jeraden
I still don't understand what you need.

You mean on the 2nd row which has field3 = 'Min. Val.', you want to find the highest value from fields 5 through 11, just for that particular row?

Exactly. Any ideas on how to accomplish that?

Yeah, unfortunately this isn't my database---I'm just working with an outdated dataset (I'm aware that the dataset is attrocious from a DB design perspective), I may end up parsing/restructuring the data, but I was jsut trying to save some time now 🙂

 

[Jeraden]

you could do:

select greatest(field5, field6, field7, field8, field9, field10, field11)
from table
where field3 = 'Min. Val.';

which would work in Oracle at least

 

[hans007]

that is not really a proper database.


if you really wanted to and you had some sort of scripting language at your disposal

you could do smething like, select field5, field7, fieldetc, from TABLE where field3="Min. Val";

and then take whatever array you got and do some calculations in that in the scripting language.

that said that table is ararnged pretty horribly.

 

[KEV1N]

Basically, people are saying that your table needs to be transposed. Then, you can use the aggregate function MAX().