Selecting *sort of* unique records

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)

Selecting *sort of* unique records

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-12 : 09:56:47

Kevin writes "I want a single(?) SQL query that will select all fields for all records where *only* a given field is unique. SELECT DISTINCT requires all fields to be unique. If I wrote SQL, I'd allow:

SELECT * DISTINCT fieldA FROM...

to mean "get all fields for all records where fieldA is unique." I'd let SQL determine which record to pull if the same value shows up more than once in fieldA, and be happy if it was the first.

I have a table of people who signed up to receive brochures. And I want to send them all e-mails. I've recorded a unique id for each request (my primary key), that I need to use in the e-mail. But the primary key field ("id") has different values for every record so I can't do "SELECT DISTINCT id,email" because it will then pull up multiple records where it shouldn't (emails are the same but IDs are different).

I could do multiple queries and get a record set of unique e-mails, and then go back and get the id for each id, but it seems there should be an easier way.

---

I apologize if this is already answered, but in looking through your site I couldn't find one. Does the answer have to do with subqueries and the EXISTS and/or IN clauses? If so, I was stumped by the documentation I found on those and am hoping you can help me out.

thanks!
-Kevin Bolduan
k.bolduan@williams-helde.com"

MakeYourDaddyProud

184 Posts

Posted - 2002-07-12 : 10:22:08

SELECT *
FROM mytable
WHERE uniquefld
JOIN (SELECT uniquefld
FROM mytable
GROUP BY uniquefld
HAVING count(*) = 1) AS ONEONLY
ON ONLEONLY.uniquefld = uniquefld

Amend col & tab as necessary

HTH

Daniel Small MIAP
www.danielsmall.com IT Factoring

MakeYourDaddyProud

184 Posts

Posted - 2002-07-12 : 10:23:29

oooppps. Take out the WHERE clause line. I have an IN version prior to that.

Daniel Small MIAP
www.danielsmall.com IT Factoring

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources