Re: SQL help with EXISTS

On 06 Dec 2012 11:15, Richard Casey wrote:

You should be able to use DISTINCT instead of GROUP BY. Also using a
common table expression may work better.

-- reduce MISCCHG recs to one per key
insert into MISCCHG2
with
mysubset as
( select distinct ASHPNBR, ABLDNGD, AMSCHRG
from MISCCHG
where <<SNIP>>
)
select a.*
from miscchg A
join mysubset b
on a.ASHPNBR = b.ashpnbr and a.ABLDNGD = b.ABLDNGD <<SNIP>>

This is the second response offered in this thread, implying that a result set that is made distinct across some subset of columns which is then joined to the original full\non-distinct TABLE [as result set] on those same columns [although the quoted example removed one column for join-on] somehow will also become distinct.?

create table qtemp/dups (r char, d char, g char, x char)
;
insert into qtemp/dups values
(0, 0, 0, 1), (0, 0, 0, 2), (0, 0, 0, 3) /* (0,0,0) */
, (1, 1, 1, 1), (1, 1, 1, 2), (1, 1, 1, 3) /* (1,1,1) */
, (1, 2, 3, 1), (1, 2, 3, 2), (1, 2, 3, 3) /* (1,2,3) */
, (1, 2, 5, 1), (1, 2, 5, 2), (1, 2, 5, 3) /* (1,2,5) */
; -- 12 rows inserted with 4 distinct (r,d,g)
with
mysubset as /* distinct across first three columns = 4 rows */
(select distinct r, d, g from qtemp/dups)
select a.* /* or count(*) */
from qtemp/dups a
join mysubset b
on a.r = b.r and a.d = b.d and a.g = b.g
; -- 12 rows, non-distinct across r, d, g
with
mysubset as /* distinct across first three columns = 4 rows */
(select distinct r, d, g from qtemp/dups)
select a.* /* or count(*) */
from qtemp/dups a
join mysubset b
on a.r = b.r and a.d = b.d /* and a.g = b.g */
; -- 18 rows, non-distinct across r, d, g
-- removing join predicate for g further explodes count