Can JOIN be used to select MAX instead of subselect?

[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 )

using 'in' and '=' are the same itself.could join be used here ?

 

[JamesLean]

Yes, you could use a join here:

SELECT cola, colb, colc
FROM <table> t1
  JOIN (
    SELECT MAX(colb) AS maxb FROM <table>
  ) t2 ON t1.colb = t2.maxb

But to be honest I would probably use your first query (with = rather than IN as you are only comparing to a single value).

--James

 

[checkai]

I would use this way because if you're doing a max() you should only be getting 1 result...and = works better than 'in'

SELECT cola, colb, colc
FROM <table> t1
  JOIN (
    SELECT MAX(colb) AS maxb FROM <table>
  ) t2 ON t1.colb = t2.maxb