How to remove duplicate data in this query?

Question

Eai personal blz?

Next I’m using this query:

select 
    products.id as product_id, 
    offers.id as offer_id, 
    companies.id as company_id, 
    products.title, 
    (ST_Distance(companies.location, 'POINT(-48.030322 -15.839689)':: geography) / 1000)  as distance

from offers 

inner join products on offers.product_id = products.id 
inner join categories on products.category_id = categories.id 
inner join offer_company on offers.id = offer_company.offer_id 
inner join companies on offer_company.company_id = companies.id 

where 
    offers.start_at <= '2016-09-02 13:07:31' and
    offers.deadline >= '2016-09-02 13:07:31' and
    products.category_id in (2) and
    ST_DWithin(companies.location, ST_SetSRID(ST_Point(-48.030322,-15.839689), 4326), 30000) 
order by 1,2,5

and the result is coming like this:

How do I limit only to the nearest company? This way:

I need to somehow display the nearest offer, however it may be in one or more stores (company_id).

Answer 1

Use the ROW_NUMBER, grouped by the field that Voce wants to use to differentiate and ordered by the value you want to use to pick up the first one (in the example that Voce placed would be the smallest Distance) After that, do a SELECT on this last command, taking only those with ROW_NUMBER = 1

SELECT * FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY product_id ORDER BY distance) as ID, *resto do select*
) TABELA
 WHERE ID = 1

Answer 2

Using the clause ROW_NUMBER next to OVER you can do it this way:

SELECT * FROM 
(
select 
    products.id as product_id, 
    offers.id as offer_id, 
    companies.id as company_id, 
    products.title, 
    (ST_Distance(companies.location, 'POINT(-48.030322 -15.839689)':: geography) / 1000)  as distance,
    ROW_NUMBER() OVER(PARTITION BY products.id,offers.id,companies.id) as n_row
from offers 

inner join products on offers.product_id = products.id 
inner join categories on products.category_id = categories.id 
inner join offer_company on offers.id = offer_company.offer_id 
inner join companies on offer_company.company_id = companies.id 

where 
    offers.start_at <= '2016-09-02 13:07:31' and
    offers.deadline >= '2016-09-02 13:07:31' and
    products.category_id in (2) and
    ST_DWithin(companies.location, ST_SetSRID(ST_Point(-48.030322,-15.839689), 4326), 30000) 
order by 1,2,5
) t WHERE n_row = 1

ROW_NUMBER will count the number of rows returned, using in conjunction with the clause OVER with the parameter PARTITION BY it will count the lines from the informed fields.

Answer 3

See if it helps.

 select 
    products.id as product_id, 
    offers.id as offer_id, 
    companies.id as company_id, 
    products.title, 
    ST_Distance(companies.location, 'POINT(-48.030322 -15.839689)':: geography) / 1000)as distance

from offers 
inner join products on offers.product_id = products.id 
inner join categories on products.category_id = categories.id 
inner join offer_company on offers.id = offer_company.offer_id 
inner join companies on offer_company.company_id = companies.id
(select * from companies where offer_company.company_id = companies.id limit 1) as companies 
on offer_company.company_id = companies.id 
where 
    offers.start_at <= '2016-09-02 13:07:31' and
    offers.deadline >= '2016-09-02 13:07:31' and
    products.category_id in (2) and
    ST_DWithin(companies.location, ST_SetSRID(ST_Point(-48.030322,-15.839689), 4326), 30000) 
order by 1,2,5