select distinct from 2 columns but only 1 is duplicate

Question

select a.subscriber_msisdn, war.created_datetime from 
(
select distinct subscriber_msisdn from wiz_application_response 
where application_item_id in 
(select id from wiz_application_item  where application_id=155)
and created_datetime between '2012-10-07 00:00' and '2012-11-15 00:00:54'
) a
 left outer join wiz_application_response war on (war.subscriber_msisdn=a.subscriber_msisdn)

the sub select returns 11 rows but when joined return 18 (with duplicates). The objective of this query is only add the date column to the 11 rows of the sub select.

I don't see table definition, sample data or PostgreSQL version. — Erwin Brandstetter, Nov 16 '12 at 14:20

score 0 · Accepted Answer · answered Nov 16 '12 at 14:21

Based on your description, it stands to reason that there are multiple created_datetime values for some of the subscriber_msisdn values which is what prompted you to use the distinct in the subquery to begin with. By joining the sub query to the original table you are defeating this. A cleaner way to write the query would be:

SELECT
    war.subscriber_msisdn
  , war.created_datetime
FROM
  wiz_application_response war
  LEFT JOIN wiz_application_item  wai
    ON war.application_item_id = wai.id
    AND wai.application_id = 155
WHERE
  war.created_datetime BETWEEN '2012-10-07 00:00' AND '2012-11-15 00:00:54'

This should return only the rows from the war table that satisfy the criteria based on the wai table. It should not be and outer join unless you wanted to return all the rows from war table that satisfied the created_datetime parameter regardless of the application_item_id parameter.

This is my best guess based on the limited information I have about your tables and what I’m assuming you’re trying to accomplish. If this doesn’t get you what you are after, I will continue to offer other ideas based on additional information you could provide. Hope this works.

score 0 · Answer 2 · edited May 23 '17 at 12:04

Can most probably simplified to this:

SELECT DISTINCT ON (1)
       r.subscriber_msisdn, r.created_datetime
FROM   wiz_application_item i
JOIN   wiz_application_response r ON r.application_item_id = i.id
WHERE  i.application_id = 155
AND    i.created_datetime BETWEEN '2012-10-07 00:00' AND '2012-11-15 00:00:54'
ORDER  BY 1, 2 DESC  -- to pick the latest created_datetime

Details depend on missing information.

More explanation here.

select distinct from 2 columns but only 1 is duplicate

2 Answers2