?
Solved

SQL DISTINCT beginner question...Probably difficult!

Posted on 2003-02-27
14
Medium Priority
?
217 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:vacalo69
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
  • +4
14 Comments
 
LVL 3

Expert Comment

by:cdillon
ID: 8035278
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
 

Author Comment

by:vacalo69
ID: 8035319
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
 
LVL 3

Expert Comment

by:galori
ID: 8035606
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 3

Expert Comment

by:cdillon
ID: 8035686
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
 
LVL 3

Expert Comment

by:cdillon
ID: 8035703
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
 

Expert Comment

by:tmorell
ID: 8036143
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
 
LVL 1

Expert Comment

by:tripst3r
ID: 8036227
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8036348
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
 
LVL 1

Accepted Solution

by:
malekam earned 400 total points
ID: 8036731
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
 

Expert Comment

by:tmorell
ID: 8037596
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8037619
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
 

Expert Comment

by:tmorell
ID: 8038120
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
 

Author Comment

by:vacalo69
ID: 8040542
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
 

Author Comment

by:vacalo69
ID: 8040553
sorry I refreshed...I will try testing now
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question