Returning Max value from Multiple rows/columns

Question

I am using SQL 2000 for this. Given the following table:

F1          D1          D2          D3          LN
==================================================
1           1/1         1/1         1/1         1
1           2/1         1/1         1/1         2
2           1/1         1/1         1/1         1
2           1/1         1/1         1/1         2

I need to get the rows that have the highest value in D1-D3. However, if they are all the same, then I would want to return all of them. So the results from the above table returned would be as follows, being that row 2 has a D1 value of 2/1, so I wouldnt need row 1, and rows 3 and 4 D values are all the same, so I would want both of those.

F1          D1          D2          D3          LN
==================================================
1           2/1         1/1         1/1         2
2           1/1         1/1         1/1         1
2           1/1         1/1         1/1         2

Thank you.

Answer 1

The data you provided is not sufficient for a solution, however you can try this:

create view T1 as
(
select F1, MAX(D1) D1, MAX(D2) D2, MAX(D3) D3
from table_3
group by F1
);
go

select *
from table_3 a inner join t1 b  
on
a.F1 = b.F1 and a.D1 = b.D1 and a.D2 = b.D2 and a.D3 = b.d3;

And this

select *
from Table_3 a
where exists 
(
    select F1, MAX(D1) D1, MAX(D2) D2, MAX(D3) D3
    from table_3 b
    group by F1
    having a.F1 = b.F1 and a.D1 = MAX(b.D1) and a.D2 = MAX(b.D2) and a.D3 = MAX(b.d3)
)