How to remove duplicate data in this query? [duplicate]

3

Hey blz personal?

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 like this:

HowcanIlimitonlytothenearestcompany_id?Thisway:

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

    
asked by anonymous 02.09.2016 / 18:32

3 answers

0

Using the ROW_NUMBER clause next to OVER you can do as follows:

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 OVER clause with the PARTITION BY parameter it will count the rows from the fields entered.

    
02.09.2016 / 19:14
0

Use ROW_NUMBER , grouped by the field you want to use to differentiate and sort by value that you want to use to get the first one (in the example you put it would be the shortest distance) After that, make a SELECT on that last command, taking only the%

SELECT * FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY product_id ORDER BY distance) as ID, *resto do select*
) TABELA
 WHERE ID = 1
    
02.09.2016 / 19:00
0

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
    
02.09.2016 / 19:19