How to get maximum and second maximum in single query

[sreenath205]

How to get maximum and second maximum using a single query

 

[nigelrivett]

select top 2 *
from tbl
order by fld desc


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[HFA]

select * from tbl where fld=(select max(fld) from tbl)
union all
select * from tbl where fld=(select max(fld)-1 from tbl)

 

[nigelrivett]

Think you are looking for something more like:

select * from tbl t1 where val = (select max(val) from tbl t2 where t2.fld = t1.fld)
union all
select * from tbl t1 where val = (select max(val) from tbl t2 where t2.fld = t1.fld and val < (select max(val) from tbl t2 where t2.fld = t1.fld))

or

select * from tbl t1 where val in (select top 2 val from tbl t2 where t2.fld = t1.fld order by val desc)

Be careful about duplicate val's


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.