How can I prevent a record from appearing based on two columns

• NineIron

  SSChampion

  Points: 12529

  More actions

  January 30, 2017 at 10:21 am

  #321214

  Sorry for the vague Subject line but, I couldn' t come up with a good summary. In the table the PrescriptionID and DuplicatePrescriptionID repeat themselves. Once as PrescriptionID and DuplicatePrescriptionID then, as DuplicatePrescriptionID and PrescriptionID. It will be apparent when you look at the table. How can I get a "unique" list of records that counts the "duplicate pair" only once?

  create table #T

  (

  VisitID varchar(30),

  PrescriptionID varchar(30),

  DuplicateSeqID int,

  DuplicateName varchar(30),

  DuplicatePrescriptionID varchar(30),

  DuplicateType char(1)

  )

  insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594059',1,'IBUPROFEN','594060','G')

  insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594059',2,'NONSTEROIDAL ANTI-INFLAMMATORY','594060','C')

  insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594059',3,'IBUPROFEN','594060','I')

  insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594060',1,'IBUPROFEN','594059','G')

  insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594060',2,'NONSTEROIDAL ANTI-INFLAMMATORY','594059','C')

  insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594060',3,'IBUPROFEN','594059','I')

  insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594064',1,'QUEtiapine Fumarate','594065','G')

  insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594064',2,'ANTIPSYCHOTIC AGENTS','594065','C')

  insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594064',3,'QUETIAPINE','594065','I')

  insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594065',1,'QUEtiapine Fumarate','594064','G')

  insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594065',2,'ANTIPSYCHOTIC AGENTS','594064','C')

  insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594065',3,'QUETIAPINE','594064','I')

• pietlinden

  SSC Guru

  Points: 63436

  More actions

  January 30, 2017 at 10:31 am

  #1925661

  NineIron - Monday, January 30, 2017 10:21 AM

  Sorry for the vague Subject line but, I couldn' t come up with a good summary. In the table the PrescriptionID and DuplicatePrescriptionID repeat themselves. Once as PrescriptionID and DuplicatePrescriptionID then, as DuplicatePrescriptionID and PrescriptionID. It will be apparent when you look at the table. How can I get a "unique" list of records that counts the "duplicate pair" only once?

  create table #T

  (

  VisitID varchar(30),

  PrescriptionID varchar(30),

  DuplicateSeqID int,

  DuplicateName varchar(30),

  DuplicatePrescriptionID varchar(30),

  DuplicateType char(1)

  )

  insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594059',1,'IBUPROFEN','594060','G')

  insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594059',2,'NONSTEROIDAL ANTI-INFLAMMATORY','594060','C')

  insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594059',3,'IBUPROFEN','594060','I')

  insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594060',1,'IBUPROFEN','594059','G')

  insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594060',2,'NONSTEROIDAL ANTI-INFLAMMATORY','594059','C')

  insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594060',3,'IBUPROFEN','594059','I')

  insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594064',1,'QUEtiapine Fumarate','594065','G')

  insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594064',2,'ANTIPSYCHOTIC AGENTS','594065','C')

  insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594064',3,'QUETIAPINE','594065','I')

  insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594065',1,'QUEtiapine Fumarate','594064','G')

  insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594065',2,'ANTIPSYCHOTIC AGENTS','594064','C')

  insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594065',3,'QUETIAPINE','594064','I')

  Could you tell us the "answer" (the correct set) and then maybe we can figure it out?

• pietlinden

  SSC Guru

  Points: 63436

  More actions

  January 30, 2017 at 10:39 am

  #1925663

  pietlinden - Monday, January 30, 2017 10:31 AM

  NineIron - Monday, January 30, 2017 10:21 AM

  Sorry for the vague Subject line but, I couldn' t come up with a good summary. In the table the PrescriptionID and DuplicatePrescriptionID repeat themselves. Once as PrescriptionID and DuplicatePrescriptionID then, as DuplicatePrescriptionID and PrescriptionID. It will be apparent when you look at the table. How can I get a "unique" list of records that counts the "duplicate pair" only once?

  create table #T

  (

  VisitID varchar(30),

  PrescriptionID varchar(30),

  DuplicateSeqID int,

  DuplicateName varchar(30),

  DuplicatePrescriptionID varchar(30),

  DuplicateType char(1)

  )

  insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594059',1,'IBUPROFEN','594060','G')

  insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594059',2,'NONSTEROIDAL ANTI-INFLAMMATORY','594060','C')

  insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594059',3,'IBUPROFEN','594060','I')

  insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594060',1,'IBUPROFEN','594059','G')

  insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594060',2,'NONSTEROIDAL ANTI-INFLAMMATORY','594059','C')

  insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594060',3,'IBUPROFEN','594059','I')

  insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594064',1,'QUEtiapine Fumarate','594065','G')

  insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594064',2,'ANTIPSYCHOTIC AGENTS','594065','C')

  insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594064',3,'QUETIAPINE','594065','I')

  insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594065',1,'QUEtiapine Fumarate','594064','G')

  insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594065',2,'ANTIPSYCHOTIC AGENTS','594064','C')

  insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594065',3,'QUETIAPINE','594064','I')

  Could you tell us the "answer" (the correct set) and then maybe we can figure it out?

  Not entirely sure if I'm supposed to UNION the two columns together and then do a GROUP BY or just a plain GROUP
  The first option would be like this:
  SELECT VisitID, PrescriptionID, COUNT(*) As dupecount
FROM (
SELECT VisitID, PrescriptionID
FROM #T
UNION ALL SELECT VisitID, DuplicatePrescriptionID
FROM #T) x
GROUP BY x.visitID, PrescriptionID;


• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  January 30, 2017 at 10:39 am

  #1925664

  Can you elaborate a little bit further on the problem and the expected output, not entirely clear what you are after?
  😎

  Quick suggestions, not certain though
  create table #T
(
  VisitID varchar(30),
  PrescriptionID varchar(30),
  DuplicateSeqID int,
  DuplicateName varchar(30),
  DuplicatePrescriptionID varchar(30),
  DuplicateType char(1)
)
insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType)
values('F1-B20170128151307684','594059',1,'IBUPROFEN','594060','G')
  ,('F1-B20170128151307684','594059',2,'NONSTEROIDAL ANTI-INFLAMMATORY','594060','C')
  ,('F1-B20170128151307684','594059',3,'IBUPROFEN','594060','I')
  ,('F1-B20170128151307684','594060',1,'IBUPROFEN','594059','G')
  ,('F1-B20170128151307684','594060',2,'NONSTEROIDAL ANTI-INFLAMMATORY','594059','C')
  ,('F1-B20170128151307684','594060',3,'IBUPROFEN','594059','I')
  ,('F1-B20170128151307684','594064',1,'QUEtiapine Fumarate','594065','G')
  ,('F1-B20170128151307684','594064',2,'ANTIPSYCHOTIC AGENTS','594065','C')
  ,('F1-B20170128151307684','594064',3,'QUETIAPINE','594065','I')
  ,('F1-B20170128151307684','594065',1,'QUEtiapine Fumarate','594064','G')
  ,('F1-B20170128151307684','594065',2,'ANTIPSYCHOTIC AGENTS','594064','C')
  ,('F1-B20170128151307684','594065',3,'QUETIAPINE','594064','I')
;
SELECT
  T.VisitID    
 ,T.PrescriptionID    
 ,T.DuplicateSeqID    
 ,T.DuplicateName    
 ,T.DuplicatePrescriptionID    
 ,T.DuplicateType
FROM  #T T
WHERE T.DuplicateSeqID = 1

  ;WITH BASE_DATA AS
(
  SELECT
   T.VisitID    
   ,ROW_NUMBER() OVER
    (
      PARTITION BY T.PrescriptionID
          ,T.DuplicatePrescriptionID
      ORDER BY  T.DuplicateSeqID ASC
    ) AS RID
   ,T.PrescriptionID    
   ,T.DuplicateSeqID    
   ,T.DuplicateName    
   ,T.DuplicatePrescriptionID    
   ,T.DuplicateType
  FROM  #T T
)
SELECT
  BD.VisitID    
 ,BD.PrescriptionID    
 ,BD.DuplicateSeqID    
 ,BD.DuplicateName    
 ,BD.DuplicatePrescriptionID    
 ,BD.DuplicateType
FROM  BASE_DATA BD
WHERE BD.RID = 1

  DROP TABLE #T;

  Output (same for both)
  

  VisitID                        PrescriptionID                 DuplicateSeqID DuplicateName                  DuplicatePrescriptionID        DuplicateType
------------------------------ ------------------------------ -------------- ------------------------------ ------------------------------ -------------
F1-B20170128151307684          594059                         1              IBUPROFEN                      594060                         G
F1-B20170128151307684          594060                         1              IBUPROFEN                      594059                         G
F1-B20170128151307684          594064                         1              QUEtiapine Fumarate            594065                         G
F1-B20170128151307684          594065                         1              QUEtiapine Fumarate            594064                         G


• NineIron

  SSChampion

  Points: 12529

  More actions

  January 30, 2017 at 10:58 am

  #1925675

  Eirikur Eiriksson - Monday, January 30, 2017 10:39 AM

  Can you elaborate a little bit further on the problem and the expected output, not entirely clear what you are after?
  😎

  Quick suggestions, not certain though
  create table #T
(
  VisitID varchar(30),
  PrescriptionID varchar(30),
  DuplicateSeqID int,
  DuplicateName varchar(30),
  DuplicatePrescriptionID varchar(30),
  DuplicateType char(1)
)
insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType)
values('F1-B20170128151307684','594059',1,'IBUPROFEN','594060','G')
  ,('F1-B20170128151307684','594059',2,'NONSTEROIDAL ANTI-INFLAMMATORY','594060','C')
  ,('F1-B20170128151307684','594059',3,'IBUPROFEN','594060','I')
  ,('F1-B20170128151307684','594060',1,'IBUPROFEN','594059','G')
  ,('F1-B20170128151307684','594060',2,'NONSTEROIDAL ANTI-INFLAMMATORY','594059','C')
  ,('F1-B20170128151307684','594060',3,'IBUPROFEN','594059','I')
  ,('F1-B20170128151307684','594064',1,'QUEtiapine Fumarate','594065','G')
  ,('F1-B20170128151307684','594064',2,'ANTIPSYCHOTIC AGENTS','594065','C')
  ,('F1-B20170128151307684','594064',3,'QUETIAPINE','594065','I')
  ,('F1-B20170128151307684','594065',1,'QUEtiapine Fumarate','594064','G')
  ,('F1-B20170128151307684','594065',2,'ANTIPSYCHOTIC AGENTS','594064','C')
  ,('F1-B20170128151307684','594065',3,'QUETIAPINE','594064','I')
;
SELECT
  T.VisitID    
 ,T.PrescriptionID    
 ,T.DuplicateSeqID    
 ,T.DuplicateName    
 ,T.DuplicatePrescriptionID    
 ,T.DuplicateType
FROM  #T T
WHERE T.DuplicateSeqID = 1

  ;WITH BASE_DATA AS
(
  SELECT
   T.VisitID    
   ,ROW_NUMBER() OVER
    (
      PARTITION BY T.PrescriptionID
          ,T.DuplicatePrescriptionID
      ORDER BY  T.DuplicateSeqID ASC
    ) AS RID
   ,T.PrescriptionID    
   ,T.DuplicateSeqID    
   ,T.DuplicateName    
   ,T.DuplicatePrescriptionID    
   ,T.DuplicateType
  FROM  #T T
)
SELECT
  BD.VisitID    
 ,BD.PrescriptionID    
 ,BD.DuplicateSeqID    
 ,BD.DuplicateName    
 ,BD.DuplicatePrescriptionID    
 ,BD.DuplicateType
FROM  BASE_DATA BD
WHERE BD.RID = 1

  DROP TABLE #T;

  Output (same for both)
  

  VisitID                        PrescriptionID                 DuplicateSeqID DuplicateName                  DuplicatePrescriptionID        DuplicateType
------------------------------ ------------------------------ -------------- ------------------------------ ------------------------------ -------------
F1-B20170128151307684          594059                         1              IBUPROFEN                      594060                         G
F1-B20170128151307684          594060                         1              IBUPROFEN                      594059                         G
F1-B20170128151307684          594064                         1              QUEtiapine Fumarate            594065                         G
F1-B20170128151307684          594065                         1              QUEtiapine Fumarate            594064                         G


  ='font-size:9.0pt;mso-bidi-font-size:11.0pt;line-height:107%;font-family:"courier>

  Here is what I need. 
  Looking at the first record, PrescriptionID=594059. The patient is given ibuprofen then, the MD orders ibuprofen again, DuplicatePrescriptionID=594060. If you look at the way the data is stored in the table there is a combination of PrescriptionID=594060 and the DuplicatePrescriptionID=594059. It's true that they are both duplicates of each other but, because 594059 is a lower number than 594060, 594060 should be the duplicate.
  
  ">

• drew.allen

  SSC Guru

  Points: 76948

  More actions

  January 30, 2017 at 11:11 am

  #1925685

  Unless I'm missing something, it's very simple.

  SELECT *
FROM #T
WHERE PrescriptionID < DuplicatePrescriptionID

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• NineIron

  SSChampion

  Points: 12529

  More actions

  January 30, 2017 at 11:22 am

  #1925692

  drew.allen - Monday, January 30, 2017 11:11 AM

  Unless I'm missing something, it's very simple.

  SELECT *
FROM #T
WHERE PrescriptionID < DuplicatePrescriptionID

  Drew

  I'm such an idiot................
  Thanx.

• Eirikur Eiriksson

  SSC Guru

  Points: 182882

  More actions

  January 30, 2017 at 11:26 am

  #1925693

  drew.allen - Monday, January 30, 2017 11:11 AM

  Unless I'm missing something, it's very simple.

  SELECT *
FROM #T
WHERE PrescriptionID < DuplicatePrescriptionID

  Drew

  
  😎

• NineIron

  SSChampion

  Points: 12529

  More actions

  January 30, 2017 at 11:29 am

  #1925697

  Eirikur Eiriksson - Monday, January 30, 2017 11:26 AM

  drew.allen - Monday, January 30, 2017 11:11 AM

  Unless I'm missing something, it's very simple.

  SELECT *
FROM #T
WHERE PrescriptionID < DuplicatePrescriptionID

  Drew

  
  😎

  Exactly.

• bgalway

  Ten Centuries

  Points: 1158

  More actions

  January 31, 2017 at 7:13 am

  #1925846

  NineIron - Monday, January 30, 2017 11:29 AM

  Eirikur Eiriksson - Monday, January 30, 2017 11:26 AM

  drew.allen - Monday, January 30, 2017 11:11 AM

  Unless I'm missing something, it's very simple.

  SELECT *
FROM #T
WHERE PrescriptionID < DuplicatePrescriptionID

  Drew

  
  😎

  Exactly.

  can you really use < on varchar data and have it act reliably? Or do you have to convert it into a number?

• NineIron

  SSChampion

  Points: 12529

  More actions

  January 31, 2017 at 7:18 am

  #1925849

  bgalway - Tuesday, January 31, 2017 7:13 AM

  NineIron - Monday, January 30, 2017 11:29 AM

  Eirikur Eiriksson - Monday, January 30, 2017 11:26 AM

  drew.allen - Monday, January 30, 2017 11:11 AM

  Unless I'm missing something, it's very simple.

  SELECT *
FROM #T
WHERE PrescriptionID < DuplicatePrescriptionID

  Drew

  
  😎

  Exactly.

  can you really use < on varchar data and have it act reliably? Or do you have to convert it into a number?

  Seems to work OK.

• drew.allen

  SSC Guru

  Points: 76948

  More actions

  January 31, 2017 at 9:39 am

  #1925900

  bgalway - Tuesday, January 31, 2017 7:13 AM

  can you really use < on varchar data and have it act reliably? Or do you have to convert it into a number?

  Yes, you can use it reliably.  The point was to be able to pick one of the two records.  It doesn't matter which of the two you pick, as long as you only pick one of them.  It doesn't matter that a different method (converting to numeric) might sometimes pick the other of the two records as long as either method will only pick one of them.  It doesn't matter that changing the collation may change the results, it still only picks one of them.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• crmitchell

  SSCarpal Tunnel

  Points: 4557

  More actions

  February 1, 2017 at 2:31 am

  #1926016

  bgalway - Tuesday, January 31, 2017 7:13 AM

  can you really use < on varchar data and have it act reliably? Or do you have to convert it into a number?

  This is perfectly reliable provided you are comparing like with like- comparison operators operate on the character codes which means you need to be aware of the collations used as these will affect the sort order.

  If the two operands have collation A you will always get the same result.
  If they both have collation B then they will still produce a consistent result but it may not be the same as that of the first query.
  If both your operands have different collations all bets are off.

• DesNorton

  SSC-Insane

  Points: 24284

  More actions

  February 1, 2017 at 2:45 am

  #1926017

  However ....
  When comparing varchars
  '100' < '99'

  ---

  How to post data/code on a forum to get the best help.

• crmitchell

  SSCarpal Tunnel

  Points: 4557

  More actions

  February 1, 2017 at 2:54 am

  #1926018

  DesNorton - Wednesday, February 1, 2017 2:45 AM

  However ....
  When comparing varchars
  '100' < '99'

  That's because '1' < '9' in all collations I am aware of - you will also find that '099' < '100'