Q

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.

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.

This was first published in December 2005

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close