Distinct () conundrum

Question

Post reply

Distinct () conundrum

heebz1

Valued Member

Points: 53
More actions
July 13, 2004 at 3:32 pm

#109090

Hello,
I've got a seemingly simple SQL task to write, which I've spent days working on and can't come up with clean code to perform the task. I've got two columns I'll call Col1 and Col2. THe values are as follows:
Col1       Col2
A            B
A            C
B            A

WHen I do a      Select distinct Col1, Col2,   I get A,B A,C B,A.   Where as I really only want distinct sets. I want to get A,B   A,c

I would be extremely grateful if anyone could offer advice on this frustrating chunk of code.

Many Thanks!!!
Scott

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jim P. SSCrazy Eights Points: 8725 More actions · Answer 1

SELECT COL1 AS COLMain, COL1 AS COL2nd

FROM TABLEA

UNION

SELECT COL2 AS COLMain, COL1 AS COL2nd

FROM TABLEA

----------------
Jim P.

A little bit of this and a little byte of that can cause bloatware.

heebz1 Valued Member Points: 53 More actions · Answer 2

Jim - Thanks for your reply! I tried that recommendation but didn't have any success. Maybe I oversimplified the statement of the problem.

THe original query was this:

SELECT distinct ti.tactical_issue_id as ti1, ti2.tactical_issue_id as ti2

FROM tactical_issue ti,

tactical_issue ti2,

product_issue pi,

ti_associated_entities tiae

where pi.tactical_issue_id = ti.tactical_issue_id and

tiae.tactical_issue_id = ti2.tactical_issue_id and

pi.patient_id = tiae.entity_id and

pi.tactical_issue_id <> tiae.tactical_issue_id and

ti.date_initiated >= (getdate()-7) and

ti2.date_initiated >= (getdate() -7)

And following the suggestion I made it this

SELECT distinct ti.tactical_issue_id as ti1, ti2.tactical_issue_id as ti2

FROM tactical_issue ti,

tactical_issue ti2,

product_issue pi,

ti_associated_entities tiae

where pi.tactical_issue_id = ti.tactical_issue_id and

tiae.tactical_issue_id = ti2.tactical_issue_id and

pi.patient_id = tiae.entity_id and

pi.tactical_issue_id <> tiae.tactical_issue_id and

ti.date_initiated >= (getdate()-7) and

ti2.date_initiated >= (getdate() -7)

UNION

SELECT distinct ti.tactical_issue_id as ti2, ti2.tactical_issue_id as ti1

FROM tactical_issue ti,

tactical_issue ti2,

product_issue pi,

ti_associated_entities tiae

where pi.tactical_issue_id = ti.tactical_issue_id and

tiae.tactical_issue_id = ti2.tactical_issue_id and

pi.patient_id = tiae.entity_id and

pi.tactical_issue_id <> tiae.tactical_issue_id and

ti.date_initiated >= (getdate()-7) and

ti2.date_initiated >= (getdate() -7)

However, I still get the repeating set as the first two entries... I thought about summing the columns and then trying to select unique values out of that but that's not a very robust idea.... Any other ideas?

46681 46682

46682 46681

46684 46683

46684 46685

46685 46683

JourneymanProgrammer SSCommitted Points: 1546 More actions · Answer 3

assumming both columns are some type of integer, trying doing a bitwise AND on both columns in your SELECT DISTINCT clause as follows:

SELECT DISTINCT(ti.tactical_issue_id & ti2.tactical_issue_id), ti.tactical_issue_id as ti1, ti2.tactical_issue_id as ti2 ...

this should take care of the duplicate scenario posted ...

heebz1 Valued Member Points: 53 More actions · Answer 4

Thanks Journey Man. Still pulling my hair out though!!!! I get this as a result when I try the Bitwise operator. I can't believe such a relatively easy concept is so hard to implement

Bitwise ti1 ti2

46680 46681 46682

46680 46682 46681

46680 46684 46683

46681 46685 46683

46684 46684 46685

SYntax says

SELECT distinct(ti.tactical_issue_id & ti2.tactical_issue_id) as Bitwise, ti.tactical_issue_id as ti1, ti2.tactical_issue_id as ti2

FROM tactical_issue ti,

tactical_issue ti2,

product_issue pi,

ti_associated_entities tiae

where pi.tactical_issue_id = ti.tactical_issue_id and

tiae.tactical_issue_id = ti2.tactical_issue_id and

pi.patient_id = tiae.entity_id and

pi.tactical_issue_id <> tiae.tactical_issue_id and

ti.date_initiated >= (getdate()-7) and

ti2.date_initiated >= (getdate() -7)

heebz1 Valued Member Points: 53 More actions · Answer 5

Hi All.... I spent the day looking through SQL Books online and can't find the proper construct for this.... The frustrating thing is that I saw it yesterday

It involved comparing the two columns at the end so that you get rid of sets that are the "same"

i.e. 1,2 2,3 2,1

I think it did something like Col1 > Col2 or something like that. That obviously won't work b/c then I'd miss any case in which the results were returned and the id of Col2 was > Col1

Still hoping for an angelic piece of advice on this one!

Phil Parkin SSC Guru Points: 246913 More actions · Answer 6

How about this one? Going back to your very first post on this, if the table is called 'test' and your columns are 'cola' and 'colb':

select distinct 'cola' =

case

when cola cola then colb

else cola

end

from test

So we ensure that the data pairs are always in ascending alpha order and do a DISTINCT on the resulting records.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

heebz1 Valued Member Points: 53 More actions · Answer 7

Thanks Phil! Great idea. I've incorporated it into my code and it works beautifully! Thanks again. Saved me alot of time writing a cursor to loop through and remove the duplicate. many thanks!!!!