return unique values in a column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

return unique values in a column

Author

Topic

jfriedl
Starting Member

4 Posts

Posted - 2012-01-06 : 12:22:39

I have a query I am running but I noticed that it is returning, in particular the cash values are duplicate values which I dont need.

SELECT
  ODS_CONTRIB_INFO.CONDES_DONOR,
  ODS_PERSON.ID,
  CONTRIB_INFO_MEASURES_MULTI.CONDES_HARD_CREDIT_AMT,
  dbo.J03_ORG_ROLES.CMPM_CAMPUS_ORGS_ID
FROM
  ( 
  SELECT ODS_CONTRIB_INFO.CONTRIB_DONOR_DESIG_ID, ODS_CONTRIB_INFO.CONDES_HARD_CREDIT_AMT, ODS_CONTRIB_INFO.CONDES_SOFT_CREDIT_AMT, ODS_CONTRIB_INFO.CONDES_MATCH_CREDIT_AMT
FROM ODS_CONTRIB_INFO
  )  CONTRIB_INFO_MEASURES_MULTI RIGHT OUTER JOIN ODS_CONTRIB_INFO ON (CONTRIB_INFO_MEASURES_MULTI.CONTRIB_DONOR_DESIG_ID=ODS_CONTRIB_INFO.CONTRIB_DONOR_DESIG_ID)
   RIGHT OUTER JOIN ODS_PERSON ON (ODS_CONTRIB_INFO.CONDES_DONOR=ODS_PERSON.ID)
   LEFT OUTER JOIN dbo.J03_ORG_ROLES ON (dbo.J03_ORG_ROLES.CMPM_PERSON_ST_ID=ODS_PERSON.ID)
  
WHERE
  ODS_CONTRIB_INFO.CONDES_DONOR  IN  ( '0000926'  )

This returns

0000926	0000926	35.00	SB101
0000926	0000926	35.00	YAPAC
0000926	0000926	25.00	SB101
0000926	0000926	25.00	YAPAC
0000926	0000926	0.00	SB101
0000926	0000926	0.00	YAPAC

I need the unique values on the CONDES_HARD_CREDIT_AMT column and Im a bit stumped at the moment.

any help is appreciated!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-06 : 12:38:50

Not quite following. You've shown what you don't want, could you show (with the same data) what exactly you do what?

--
Gail Shaw
SQL Server MVP

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-06 : 12:39:05

One way would be to use a max (or other aggregate function) on the columns that are causing the repetition. For example:

SELECT
  ODS_CONTRIB_INFO.CONDES_DONOR,
  ODS_PERSON.ID,
  CONTRIB_INFO_MEASURES_MULTI.CONDES_HARD_CREDIT_AMT,
  MAX(dbo.J03_ORG_ROLES.CMPM_CAMPUS_ORGS_ID) AS CMPM_CAMPUS_ORGS_ID
FROM
  ( 
  SELECT ODS_CONTRIB_INFO.CONTRIB_DONOR_DESIG_ID, ODS_CONTRIB_INFO.CONDES_HARD_CREDIT_AMT, ODS_CONTRIB_INFO.CONDES_SOFT_CREDIT_AMT, ODS_CONTRIB_INFO.CONDES_MATCH_CREDIT_AMT
FROM ODS_CONTRIB_INFO
  )  CONTRIB_INFO_MEASURES_MULTI RIGHT OUTER JOIN ODS_CONTRIB_INFO ON (CONTRIB_INFO_MEASURES_MULTI.CONTRIB_DONOR_DESIG_ID=ODS_CONTRIB_INFO.CONTRIB_DONOR_DESIG_ID)
   RIGHT OUTER JOIN ODS_PERSON ON (ODS_CONTRIB_INFO.CONDES_DONOR=ODS_PERSON.ID)
   LEFT OUTER JOIN dbo.J03_ORG_ROLES ON (dbo.J03_ORG_ROLES.CMPM_PERSON_ST_ID=ODS_PERSON.ID)
  
WHERE
  ODS_CONTRIB_INFO.CONDES_DONOR  IN  ( '0000926'  )
GROUP BY
  ODS_CONTRIB_INFO.CONDES_DONOR,
  ODS_PERSON.ID,
  CONTRIB_INFO_MEASURES_MULTI.CONDES_HARD_CREDIT_AMT

Just be aware though, that if you use group by on data type FLOAT, some unexpected results may show up. If the data type of the numeric column is an exact numeric type such as decimal or int, you would be fine.

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-06 : 12:45:39

data is data

you can't just mash it all willy nilly

you have to make a decision about what you want to see

Whem you say

>> in particular the cash values are duplicate values which I dont need.

what does that mean? those values BELONG to something

If it's because of a grouping and its a presentation, then you do it in that layer

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

jfriedl
Starting Member

4 Posts

Posted - 2012-01-06 : 12:46:00

That works perfectly, I was thinking either using max or I was messing around with PARTITION but could not get it to work.

Thanks you!!!!

quote:

Originally posted by sunitabeck

One way would be to use a max (or other aggregate function) on the columns that are causing the repetition. For example:

SELECT
  ODS_CONTRIB_INFO.CONDES_DONOR,
  ODS_PERSON.ID,
  CONTRIB_INFO_MEASURES_MULTI.CONDES_HARD_CREDIT_AMT,
  MAX(dbo.J03_ORG_ROLES.CMPM_CAMPUS_ORGS_ID) AS CMPM_CAMPUS_ORGS_ID
FROM
  ( 
  SELECT ODS_CONTRIB_INFO.CONTRIB_DONOR_DESIG_ID, ODS_CONTRIB_INFO.CONDES_HARD_CREDIT_AMT, ODS_CONTRIB_INFO.CONDES_SOFT_CREDIT_AMT, ODS_CONTRIB_INFO.CONDES_MATCH_CREDIT_AMT
FROM ODS_CONTRIB_INFO
  )  CONTRIB_INFO_MEASURES_MULTI RIGHT OUTER JOIN ODS_CONTRIB_INFO ON (CONTRIB_INFO_MEASURES_MULTI.CONTRIB_DONOR_DESIG_ID=ODS_CONTRIB_INFO.CONTRIB_DONOR_DESIG_ID)
   RIGHT OUTER JOIN ODS_PERSON ON (ODS_CONTRIB_INFO.CONDES_DONOR=ODS_PERSON.ID)
   LEFT OUTER JOIN dbo.J03_ORG_ROLES ON (dbo.J03_ORG_ROLES.CMPM_PERSON_ST_ID=ODS_PERSON.ID)
  
WHERE
  ODS_CONTRIB_INFO.CONDES_DONOR  IN  ( '0000926'  )
GROUP BY
  ODS_CONTRIB_INFO.CONDES_DONOR,
  ODS_PERSON.ID,
  CONTRIB_INFO_MEASURES_MULTI.CONDES_HARD_CREDIT_AMT

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-06 : 13:01:33

WHY NOT USE MIN?

How about AVG

Why not SUM

Or COUNT?

Or a formula?

What else

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources