Getting Distinct Values - SQL Server Forums

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

Transact-SQL (2000)

Getting Distinct Values

Author

Topic

phencerx
Starting Member

2 Posts

Posted - 2008-02-04 : 16:33:00

So I've hit a brick wall. I'm working on creating a script to do some database cleanup but I'm having problems getting a query to bring the exact data I want.

When I run:

select distinct(SUBSTRING(description,1,(LEN(description) - 8)))as description
from documenttemplates
where description like '%(%)'

I get results

description
--------------------------------------------------
Agreement to Provide Insurance
Appraisal
Appraisal Letter
Appraisal Review
Auto Transfer Authorization
Business Purpose Statement
CIP Addendum
Credit Memo
Disbursement
DOT
Environmental Report
FCRA Post-closing Notice
Flood Verification
Good Faith Estimate
HMDA
Insurance Disclosure
Itemization of Amount Financed
Loan Agreement
Loan Doc Checklist
Note
Notice of Availability of Title
Notice to Home Loan App - Credi
Power of Attorney
Pre-closing Docs
Right to Cancel
SECURITY AGREEMENT
Settlement Statement
Settlement Stmt / Disbursement
TIL
Title Policy
UCC-1

But when I add in the ID field to the query

select (SUBSTRING(description,1,(LEN(description) - 8)))as description, id
from documenttemplates
where description like '%(%)'

My results turn into:

description id
-------------------------------------------------- -----------
Agreement to Provide Insurance 175
Agreement to Provide Insurance 194
Agreement to provide insurance 27
Agreement to Provide Insurance 276
Agreement to Provide Insurance 277
Agreement to provide Insurance 40
Agreement to provide Insurance 51
Agreement to Provide Insurance 56
Agreement to Provide Insurance 59
Appraisal 103
Appraisal 115
Appraisal 79
Appraisal 90
etc.

I understand why it's doing this, but I'm at a loss to where I can only pull the first instance of each document with it's id so I could get:

description id
-------------------------------------------------- -----------
Agreement to Provide Insurance 175
Appraisal 103
etc.

I'm sure it's something simple that I'm overlooking, but any help would be much appreciated.

Thanks

hey001us
Posting Yak Master

185 Posts

Posted - 2008-02-04 : 17:02:46

here you go:
select (SUBSTRING(description,1,(LEN(description) - 8)))as description, max(id)
from documenttemplates
where description like '%(%)'
group by (SUBSTRING(description,1,(LEN(description) - 8)))

hey

phencerx
Starting Member

2 Posts

Posted - 2008-02-04 : 17:07:55

Like I said, something simple that I was overlooking.

Thanks hey.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources