select distinct problem...

• ctklein

  SSChasing Mays

  Points: 624

  More actions

  June 25, 2003 at 1:32 pm

  #81081

  Greetings,

  This is probably a really stupid question so I appologize in advance.

  I have a table; intId,vchrcode,vchrdescription,lngvalue (example below)

  12WOOD PANELWOOD PANEL1

  13ALUMINUMALUMINUM1

  14COMP CLAPCOMP CLAP0.98

  15COMP CLAPCOMP CLAP0.98

  16WOODWOOD1

  17WOOD SHINGWOOD SHING1

  I need to select records from this table but I only want to show a unique combination of vchrcode,vchrdescription, lngvalue. So in the above data I would only get one occurance of 'COMP CLAP'

  The closest I can get to is this:

  select vchrCode,vchrDescription,lngValue from TableName

  group by vchrCode,vchrdescription,lngvalue

  HAVING COUNT(vchrCode)=1

  but I need to also retrieve the intID, which seems to invalidate the count=1

  thoughts, comments, suggestions?

  Thanks,

  Chris

• MarkusB

  SSC-Dedicated

  Points: 37370

  More actions

  June 26, 2003 at 3:20 am

  #463189

  Try this

  select MIN (intID),vchrCode,vchrDescription,lngValue from TableName

  group by vchrCode,vchrdescription,lngvalue

  HAVING COUNT(vchrCode)=1

  [font="Verdana"]Markus Bohse[/font]

• ctklein

  SSChasing Mays

  Points: 624

  More actions

  June 26, 2003 at 7:05 am

  #463225

  Actually I was able to knock out something last evening:

  Select A.* from ExteriorWallCodes A INNER JOIN

  (Select min(intID) as intID,vchrCode from ExteriorWallCodes

  Group by vchrCode) B

  ON (a.intID=b.intID and A.vchrCode = B.vchrCode)

  order by A.intID

  Thanks for the help though... I just need to look at both because from a table of 41 items, yours returns 29 and mine returns 27, just need to see whats what and why 🙂

  regards,

  Chris