SQL DISTINCT beginner question...Probably difficult!

Hi there,

I have a table "Reserved" with the following two columns and five records:

Rec_ID    CID
-------------
 80        14
 80        6
 80        15
 79        6
 78        6

I need a query which is going to do the following:

"Return all rows besides the duplicates that do not have a CID of 14".
I know it might sound weird. Let me explain. There are 3 duplicate records with Rec_ID = 80 and out of these we need the ONE with CID = 14. That is one record "group" that we need.
Also there are two non-duplicate values(REC_ID = 79,78), which are another "group". All we need is the UNION of these two "groups", which will give us:

Rec_ID    CID
-------------
 80        14
 79        6
 78        6

People I would be extremely obliged if you can help me on this by providing me a SQL query.

Thanks a lot in advance

Chris
vacalo69Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cdillonCommented:
You could use this:

select rec_id,
(select top 1 cid from reserved where r.rec_id = rec_id and cid <> 14) as mycid
from reserved r
where r.cid <> 14
group by r.rec_id
0
vacalo69Author Commented:
This returns:

Rec_ID    CID
-------------
80        6
79        6
78        6

and not

Rec_ID    CID
-------------
80        14
79        6
78        6

Thanks though. Can you fix it?
Thanks
0
galoriCommented:
select recID,14
from reserved t1
where 14 in (select cid from reserved t2 where t1.recID = t2.recID)
group by recID
union
select recID,CID
from reserved
where recID not in
(select recID
from reservedt1
where 14 in (select cid from reservedt2 where t1.recID = t2.recID)
group by recID)


yields the following results:

recID   CID
-----------
78     6
79     6
80     14
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

cdillonCommented:
You could use this:

select rec_id,
(select top 1 cid from reserved where r.rec_id = rec_id and cid <> 14) as mycid
from reserved r
where r.cid <> 14
group by r.rec_id
0
cdillonCommented:
Sorry, I refreshed the page:  Try

select rec_id,
(select top 1 cid from reserved where r.rec_id = rec_id ) as mycid
from reserved r
group by r.rec_id
0
tmorellCommented:
Try this:

select rec_id, cid, NULL
from reserved
where cid = 14
union
select rec_id, max(cid), sum(1)
from reserved
group by rec_id
having sum(1) = 1

This should give you the rowset you want, just ignore the third column.
0
tripst3rCommented:
Are these real values? As in, are you looking for something that works with literally this data, or for something that would work in parallel cases when you're not sure of one piece or another?

If this is your real data, you could use
SELECT rec_id, cid
FROM reserved
WHERE cid = 14 OR rec_id <> 80

If this is just sample data, and you would formulate the query more as "Give me the highest cid from any single or group of identical rec_ids," then you'll want something more like cdillon's second example. Union can be pretty intensive when you get much data involved.


0
Scott PletcherSenior DBACommented:
The query below should return any row with a unique Rec_ID AND any row(s) that have duplicate Rec_IDs and a CID = 14; it will NOT return a row for duplicates that DON'T have a CID = 14.


SELECT Rec_ID, CID
FROM Reserved R1
WHERE NOT EXISTS(
    SELECT 1
    FROM Reserved R2
    WHERE R1.Rec_ID = R2.Rec_ID AND R1.CID <> R2.CID)
OR (EXISTS(
    SELECT 1
    FROM Reserved R2
    WHERE R1.Rec_ID = R2.Rec_ID AND R1.CID <> R2.Rec_ID)
    AND CID = 14)
0
malekamCommented:
This is the most simple and readable solution I could find:

SELECT R.Rec_ID, R.CID
FROM Reserved AS R
WHERE R.Rec_ID NOT IN (SELECT RI.Rec_ID
                       FROM Reserved AS RI
                       GROUP BY RI.Rec_ID
                       HAVING Count(RI.Rec_ID) > 1)
OR R.CID=14
ORDER BY R.Rec_ID

Good Luck.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tmorellCommented:
Left out something:

--Distinct Group where CID = 14
select distinct rec_id, cid, NULL
from reserved
where cid = 14
UNION
--Non-duplicates where CID <> 14
select rec_id, max(cid), sum(1)
from reserved
where cid <> 14
group by rec_id
having sum(1) = 1

Unions kinda have a bad rap but they're not so bad, especially in SQL Server.  Many times, a UNION will significantly out-perform an OR predicate, even on very large tables.
0
Scott PletcherSenior DBACommented:
Do you have actual, specific results of a UNION, which it seems to forces two table scans rather than one, performing better?  I just can't see how that could be.
0
tmorellCommented:
I guess I should have been more specific.  The point would be to avoid doing table scans to begin with unless the tables involved are very small. Take advantage of indexes where you can and within reason.  Indexes have tradeoffs too.

I have run across cases when examining the execution plan where a doing one clustered index scan was slower than doing 2 clustered index seeks (which are much faster) and a UNION.  I guess my point is, examine the execution plan and us it to determine your best course of action.
0
vacalo69Author Commented:
This returns:

Rec_ID    CID
-------------
80        6
79        6
78        6

and not

Rec_ID    CID
-------------
80        14
79        6
78        6

Thanks though. Can you fix it?
Thanks
0
vacalo69Author Commented:
sorry I refreshed...I will try testing now
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.