CODEWITHSUNDEEP
sqloracle-database
xn dx
xn dx

Reputation: 793

Selecting only duplicate rows, kinda

I'm really not sure on the best way to explain this, but we have a database which has a unique ID for each employee, a description, and a flag for current.

SELECT COUNT("Current_Flag"),
  "Employee_Number"
FROM "Employment_History"
WHERE "Current_Flag" = 'Y'
GROUP BY "Employee_Number" ;

I'm trying to return the unique ID for every case where they have two current flags set, but I don't even know where to begin. I've tried a subselect which didn't work, I'm sure the answer is quite simple but I've just been told I only have 7 minutes to do it, so panicked and thought I'd ask here. Sorry all :/

Upvotes: 0

Views: 195

Answers (5)

user359040
user359040

Reputation:

Add a HAVING clause to your current query - like so:

select count("Current_Flag"), 
       "Employee_Number" 
from "Employment_History" 
where "Current_Flag" = 'Y' 
group by "Employee_Number"
having count("Current_Flag") >= 2

(Change the condition to =2 if you only want exactly 2 matches.)

Upvotes: 3

CloudyMarble
CloudyMarble

Reputation: 37566

(UNTESTED STATEMENT)

SELECT Id FROM TABLE1 T1 WHERE T1.Id NOT IN(
SELECT Id FROM TABLE2 T2 WHERE (T1.Flag1 = T2.Flag1) AND (T1.Id <> T2.Id))

Upvotes: 0

xQbert
xQbert

Reputation: 35323

Self Join table return only rows where 2 "Flags" are alike exclude rows that match on UniqueID returning al instances of ID having matches on flags.

Select A.ID
from table A
INNER JOIN table B 
on A.Flag1=B.Flag1 and A.Flag2=B.Flag2
Where A.ID <> B.ID

Upvotes: 0

Nuno
Nuno

Reputation: 1163

select employee_name, count(employee_name) as cnt 
group by employee_name
where cnt>1

I guess this will do the trick. Adapt it to the fields on your DB.

Upvotes: 0

fin1te
fin1te

Reputation: 4351

Try something like this (untested!). You might have to play around with HAVING/COUNT to get it to work

SELECT unique_id, description, flag FROM table GROUP BY unique_id HAVING COUNT(unique_id) > 1

Upvotes: 0

Related Questions