Select unique ROW in one to many

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 2000 Forums

SQL Server Administration (2000)

Select unique ROW in one to many

Author

Topic

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2010-06-14 : 14:16:58

I have a table that contains many rows. I can select the unique field I want using the following:

select CLEARINGHOUSE_TRACE_NUMBER
from IB837_Holding where (CPP_LOAD_INDICATOR = 'Y' or CPP_LOAD_INDICATOR = 'N')
and CLEARING_HOUSE = 'GATEWAY EDI'
group by (CLEARINGHOUSE_TRACE_NUMBER)

What I need is one, entire row of data per CLEARINGHOUSE_TRACE_NUMBER from IB837_Holding. I'm trying to avoid putting all columns in the group by clause and I know there just has to be a better solution but at the moment, I appear to be a little brain-dead. Google has provided ways to do it but within 2005, not 2000. Any better idea on how to accomplish this?

Terry

-- A word to the wise ain't necessary - it's the stupid ones that need the advice. -- Bill Cosby

jaynichols
Starting Member

18 Posts

Posted - 2010-06-14 : 14:56:11

what about:

select * into #temp from sourcetable
union
select * from sourcetable

The union statment will only allow unique rows to be placed in #temp.

Dirt biking forever!

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2010-06-14 : 15:49:51

Sorry! Can this be moved to SQL 2000 transact-sql or programming forum??

Terry

-- A word to the wise ain't necessary - it's the stupid ones that need the advice. -- Bill Cosby

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-06-15 : 02:55:52

select distinct *
from IB837_Holding where (CPP_LOAD_INDICATOR = 'Y' or CPP_LOAD_INDICATOR = 'N')
and CLEARING_HOUSE = 'GATEWAY EDI'

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2010-06-15 : 08:07:40

I thought that but I should have been clearer on the OP. There are approx. 40 fields of which 20 would be unique. I didn't want to do it this way (covering query of the 20 or so fields), as I know there has to be a better solution, but it works:

select CLEARINGHOUSE_TRACE_NUMBER, [RUNID], [CLEARING_HOUSE], [CLEARINGHOUSE_TRACE_NUMBER], [CLAIM_TYPE], [INSURED_ID_1A],[PLAN_GROUP_11C], [BILLING_PROVIDER_FNAME_33],
[BILLING_PROVIDER_LNAME_33], [BILLING_PROVIDER_ORGNAME_33], [BILLING_PROVIDER_NPI_33A], [BILLING_PROVIDER_TAXID_25], [BILLING_PROVIDER_SSN_25], [TOTAL_CHARGE_28], [TRANSLATION_RESULTS],
[TRANSLATION_ERROR], [CPP_LOAD_INDICATOR], [CPP_LOAD_ERROR], [CPP_LOAD_DATE], [CPP_LOAD_CLAIM_ID]
from IB837_Holding where (CPP_LOAD_INDICATOR = 'Y' or CPP_LOAD_INDICATOR = 'N')
and CLEARING_HOUSE = 'GATEWAY EDI'
group by CLEARINGHOUSE_TRACE_NUMBER, [RUNID], [CLEARING_HOUSE], [CLEARINGHOUSE_TRACE_NUMBER], [CLAIM_TYPE], [INSURED_ID_1A],[PLAN_GROUP_11C], [BILLING_PROVIDER_FNAME_33],
[BILLING_PROVIDER_LNAME_33], [BILLING_PROVIDER_ORGNAME_33], [BILLING_PROVIDER_NPI_33A], [BILLING_PROVIDER_TAXID_25], [BILLING_PROVIDER_SSN_25], [TOTAL_CHARGE_28], [TRANSLATION_RESULTS],
[TRANSLATION_ERROR], [CPP_LOAD_INDICATOR], [CPP_LOAD_ERROR], [CPP_LOAD_DATE], [CPP_LOAD_CLAIM_ID]
ORDER BY CLEARINGHOUSE_TRACE_NUMBER

I can't wait to get this on SQL2005 and utilize some of the newer (to me!) features. Thanks for the suggestions, much appreciated.

Terry

-- A word to the wise ain't necessary - it's the stupid ones that need the advice. -- Bill Cosby

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources