Select specific Columns for MAX value of a column

[leo6]

From the given table ,
COLA COLB COLC COLD
---- ---- ---- ----
1 4 a 1
2 6 s 2
4 9 d 3
3 17 e 1
5 11 f 2
6 15 r 3

I would like to retreive,
COLA COLB COLC
---- ---- ----
3 17 e


ie., values for COLA,COLB and COLC having max value for COLB .

I know we can use,
select COLA,COLB,COLC from table
where COLB in (select max(COLB) from table )

but,is there any other way to write this query for better execution ?

 

[mrdenny]

Use = not IN.

select COLA,COLB,COLC 
from table
where COLB = (select max(COLB) from table )

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[leo6]

No,,both = and IN are the same.I was wondering if join could be used??

 

[mrdenny]

Yes you could use a join, with an inline subquery. I don't think it will give you an performance difference.

select COLA,COLB,COLC 
from table
join (select max(COLB) colb from table) t2 on table.COLB = t2.COLB

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)