sql - Select 2 most expensive cars for every city -

i select 2 expensive cars every city.

ddl below:

create table city_car  (     id bigserial,     city varchar(255),     car varchar(255),     price int,     primary key (id) );  insert city_car(city, car, price)  values     ('los angeles', 'kia rio', 550),     ('los angeles', 'audi a4', 1800),     ('los angeles', 'lexus nx', 2000),     ('los angeles', 'chevrolet camaro', 2800),     ('los angeles', 'mazda 6', 1300),     ('moscow', 'mazda 3', 1000),     ('moscow', 'kia cerato', 1000),     ('moscow', 'lexus nx', 2100),     ('moscow', 'lexus lx', 5000),     ('moscow', 'bmw x6', 5000),     ('prague', 'skoda octavia', 1000); 

output should same list below columns:

city          name              price ----------------------------------------- 'los angeles' 'lexus nx'         2000  'los angeles' 'chevrolet camaro' 2800  'moscow'      'lexus lx'         5000  'moscow'      'bmw x6'           5000  'prague'      'skoda octavia'    1000 

greatest-n-per-group problems typically solved using window functions:

select city,car,price (   select *,           dense_rank() on (partition city order price desc) rnk   city_car ) t rnk <= 2 order city, price desc; 

depending on how want deal highest value appearing twice, can change dense_rank() row_number()

online example: http://rextester.com/alkz85776