Geek Answers Handbook

GROUP or DISTINCT after JOIN returns duplicates

I have two tables, productsand meta. They relate to a 1: N ratio, where each product line has at least one meta line through a foreign key.

(namely: SQLfiddle: http://sqlfiddle.com/#!15/c8f34/1 )

I need to join these two tables, but I need to filter only unique products. When I try this query, everything is fine (4 lines returned):

SELECT DISTINCT(product_id)
FROM meta JOIN products ON products.id = meta.product_id

but when I try to select all columns, the DISTINCT rule no longer applies to the results, since 8 rows are returned instead of 4.

SELECT DISTINCT(product_id), *
FROM meta JOIN products ON products.id = meta.product_id

I tried many approaches, for example, trying to execute DISTINCTor GROUP BYin a subquery, but always with the same result.

+2
4

, / :

SELECT *
FROM   products p
JOIN  (
   SELECT DISTINCT ON (product_id) *
   FROM   meta
   ORDER  BY product_id, id DESC
   ) m ON m.product_id = p.id;

meta products, .

, ORDER BY , , . @Craig @Clodoaldo . meta id.

SQL Fiddle.

DISTINCT ON:

, . . , , :

SELECT p.*, sub.meta_id, m.product_id, m.price, m.flag
FROM  (
   SELECT product_id, max(id) AS meta_id
   FROM   meta
   GROUP  BY 1
   ) sub
JOIN meta     m ON m.id = sub.meta_id
JOIN products p ON p.id = sub.product_id;

id , SELECT p.*, m.*. ( id .)

, :

+3

, DISTINCT ON, PostgreSQL:

SELECT 
  DISTINCT ON(product_id)
  * 
FROM meta 
INNER JOIN products ON products.id = meta.product_id;

http://sqlfiddle.com/#!15/c8f34/18

, ORDER BY ; .

+3

distinct on, @Craig answer, order by, . SQL Fiddle

select distinct on(m.product_id) * 
from
    meta m
    inner join
    products p on p.id = m.product_id
order by m.product_id, m.id desc;
+1
source

You can use a subquery to identify max (ID) for each product, and then use it in a superquery to collect the details that you want to display:

SELECT q.product_id, meta.* from
(SELECT product_id, max(meta.ID)
 FROM meta JOIN products ON products.id=meta.product_id 
 GROUP BY product_id) q 
JOIN meta ON q.max=meta.id;

This is not the only solution!

A quick comparison of the use of DISTINCT ON solutions suggests that it is slower ( http://sqlfiddle.com/#!15/c8f34/38 ). It avoids the complete sorting by identifier and prefers sequential scanning.

0
source

More articles:


All Articles