Answer

Multiple rows with same value in one column

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. I am only interested in seeing the rows for all the emp_no that shows more than once.

Rudy Limeback
By
Published: 02 Dec 2005

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 development languages