Selecting details of the record having MAX value for a particular col

[leo6]

Is there any other way to write the below query ?
======================================================
SELECT A.COL1 N1, A.COL2 N2, A.COL3 N3,
FROM TAB1 A

WHERE A.COL4 = CONSTANT
And A.COL5 =

(SELECT MAX (B.COL5) from TAB1 B
WHERE B.COL4 = CONSTANT
And A.COL1=B.COL1)
========================================================
Objective is to retreive col1,col2 and col3 values of the record with col4=constant and col5 having maximum value in the table.

 

[Pattycake245]

The way you have it would seem to be fine. Is this not producing the desired results? Is it taking a long time to execute? Another way to write it would be something like:

SELECT A.COL1 N1, A.COL2 N2, A.COL3 N3,
FROM TAB1 A join
(select A.COL1, max(col5) from TAB1 WHERE A.COL4 = CONSTANT group by COL1) B
on A.COL1=B.COL1

Tim