1

We're running Oracle 11g on Windows Server 2008 R2

I currently have an application that used nationally for managing our recruiting system, we're a contract engineering staffing firm.

Now, we have a pretty decently sized database of candidates we've previously worked with and I used Oracle Text to make the attached resumes searchable, which works out great, the only issue is that we often send the same candidate to multiple jobs in order to put them to work faster, which means I may get the same candidate's resume turning up 6 or 7 times in the search results, I'm looking to have it filter out duplicates based on candidate's name and recruiter name since I don't want to only filter out candidate names since some people have the same name, but usually 1 recruiter works with 1 candidate, I know it's not perfect, but could help to narrow things down.

I've tried a few things to remove the dupes, but I can't get it to work, here's the current query that we use to generate the search results.

select score(1) relevance, "PKEY", "DATE_SUB", "CLIENT", "CANDIDATE", "RECRUITER", "SALES", dbms_lob.getlength("RESUME") "RESUME", +"MIMETYPE", "FILENAME", "POSITION", "AVAILABILITY", "RATE", "ISSUES", "WHEN_INT", "FEEDBACK", "NOTES" from "SUBMITTALS" where CONTAINS (resume, :P11_SEARCH, 1) > 0 order by 1 desc

Any ideas?

Thanks again.

Improve this question
1
  • So you're searching the resume column. Does this mean that the same candidate is in multiple records with other related identical fields, such as the resume? Commented Nov 2, 2012 at 18:48

1 Answer 1

Reset to default
1

You could use analytic functions to remove the duplicates:

select * from ( select score(1) relevance, "PKEY", "DATE_SUB", "CLIENT", "CANDIDATE", "RECRUITER", "SALES", dbms_lob.getlength("RESUME") "RESUME", "MIMETYPE", "FILENAME", "POSITION", "AVAILABILITY", "RATE", "ISSUES", "WHEN_INT", "FEEDBACK", "NOTES", row_number() over (partition by CANDIDATE, RECRUITER order by PKEY) rn from "SUBMITTALS" where CONTAINS (resume, :P11_SEARCH, 1) > 0 ) where rn = 1 order by 1 desc

This will return one row per candidate/recruiter pair.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.