Select all orders except the max order for each distinct customer

Sorry for the poor formatting but as part of a larger problem, I have created a query that produces this table:

id id2    
4  7    
4  6    
1  3    
1  2    
1  1

How would I extract the rows that don’t have the highest id2 for each id1.

What I want:

id id2    
4  6    
1  2    
1  1

I can only seem to figure out how to get rid of the max id2 overall but not for each distinct id1. Any help on actually differentiating the max id2 for each id1 would be appreciated.

Answer

You can try below way –

select a.id, a.id2 
from tablename a
where a.id2 <> (select max(a1.id2) from tablename a1 where a.id=a1.id)