I'm doing a search from one table and my goal is to show only the rows with the same value in one of the columns. For example,
select emp_no, valid_from, valid_to from uddevalla.employee_degree_occupation where valid_from <= '&dag2' and valid_to >= '&dag1' and company_id = '01'
This query will return a lot of rows, but I am only interested in seeing the rows for all the emp_no that shows more than once.
Based on the fact that you have three columns including a date range in the SELECT, I'm going to assume that there are no completely duplicate employee date ranges. In other words, you will get exactly the same results by adding a GROUP BY with a HAVING COUNT(*) > 1 clause, if the GROUP BY contains all three columns.
What you're looking for is employees with multiple date ranges that satisfy a specified period in the WHERE clause, and those date ranges are all different.
The way to do this is first to determine which employees have multiple date ranges. For this purpose, we cannot have the date range in the SELECT, just the employee number. This is "the same value in one of the columns" as you mentioned in the question. With only the employee number in both the SELECT and GROUP BY, the HAVING clause will work as expected.
select emp_no from uddevalla.employee_degree_occupation where valid_to >= '&dag1' and valid_from <= '&dag2' and company_id = '01' group by emp_no having count(*) > 1
This isn't the entire solution, though, because it only identifies the employees. Now we need to see the different date ranges that these employees have. To do this, we simply use the GROUP BY query as a subquery, and select the date range in the outer query.
select emp_no , valid_from , valid_to from uddevalla.employee_degree_occupation where valid_to >= '&dag1' and valid_from <= '&dag2' and company_id = '01' and emp_no in ( select emp_no from uddevalla.employee_degree_occupation where valid_to >= '&dag1' and valid_from <= '&dag2' and company_id = '01' group by emp_no having count(*) > 1 )
Notice that the outer query needs to specify the date range too, otherwise you would get all date ranges for each employee, and not just date ranges in the specified period.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback, SQL Consultant, r937.com
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback.continue reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.