row with highest column A/B/C/D (was "SQL: is this possible?")

irenicuz · #1 (**permalink**) 07-20-06, 05:06

Hi i am wondering if this is possible with SQL:

Record Nr/Field A/Field B/Field C/Field D
1/100/200/500/1000
2/50/300/250/500
3/50/100/1000/500

Is there any possibility with SQL to select all data from this table where the following criteria are used:

Field A highest ? Yes: take this record
No: Field B highest ? Yes: take this record
No: Field C highest ? Yes: take this record
No: Field D highest ? Yes: take this record

This will result in record 1 to be taken (Field A is highest)

Other data:

Record Nr/Field A/Field B/Field C/Field D
1/100/200/500/1000
2/100/200/750/500
3/50/100/1000/500

This will result in record 2 to be taken (Field C is higest)

Any help is appreciated, Thanks

andrewst · #2 (**permalink**) 07-20-06, 05:14

If I understand you correctly, this SQL will return all the rows with the one you want at the top:

Code:

select * from mytable order by a desc,b desc,c desc,d desc;

All you then need to do is take just the first record from that result set. How you do that varies from one DBMS to another.

r937 · #3 (**permalink**) 07-20-06, 05:15

if a row (not a record) has 4 columns (not fields), then one of them is going to have the highest value in the row, and so you want that row (not record) to be returned?

sounds like you want all rows returned, because each row will have a highest column value amongst its 4 column values

or do you want values compared amongst all rows at the same time? in that case column C in row 2 does not have the highest value

confused

irenicuz · #4 (**permalink**) 07-20-06, 05:18

Quote:

Originally Posted by andrewst

If I understand you correctly, this SQL will return all the rows with the one you want at the top:

Code:

select * from mytable order by a desc,b desc,c desc,d desc;

All you then need to do is take just the first record from that result set. How you do that varies from one DBMS to another.

I just started to experiment with this

This MUST be the solution i was looking for. Thanks

Edit: I had to select the LAST record to get the correct record (MS Access). Strange, but it works.

Internet Services

Web Development

Digital Marketing

Consumer Tech