How to get the similar values from a particular column in a table

Question

5.00/5 (1 vote)

See more:

I have one EMP_A table. Below is the table value -
ENAME DEPT-NAME DESIGNATION SALARY DATE-OF-JOIN

SQL

FARAH    RESEARCH      ANALYST       30000     Dec 03, 1991
TOMAR    SALES         SALESMAN      15000     Sep 08, 2001
SAXENA   SALES         SALESMAN      12500     Sep 28, 1999
TOMAR    SALES         SALESMAN      14500     Feb 22, 1997
ANAND    RESEARCH      CLERK         11000     Jan 12, 1993

How I will find the employees with similar names and display their designation, department and data of join.
Please help me out.
I have tried this below query, but some error is there.

SQL

SELECT ENAME, DESIGNATION, DEPT_NAME, DATE_OF_JOIN, COUNT(*) AS CountOf FROM EMP_A GROUP BY ENAME, DATE_OF_JOIN HAVING COUNT(*)>1;

Posted 10-Sep-15 1:54am

Bittu14

Updated 10-Sep-15 19:36pm

Schatak

v2

Add a Solution

Comments

Schatak 11-Sep-15 2:10am

If i understand you want records for only those employees having same name in your query?

5 solutions

Solution 4

Bittu14,

If you are looking for duplicate entry with the same name (not similar) your query is:

SQL

SELECT 
 ENAME, COUNT(*) AS CountOf 
FROM 
 EMP_A 
GROUP BY ENAME
HAVING COUNT(*)>1;

Then, if you need to show additional fields for these duplicate names you may consider using it as subquery:

SQL

SELECT
 ENAME, DESIGNATION, DEPT_NAME, DATE_OF_JOIN
FROM
 EMP_A 
WHERE ENAME IN 
  (
   SELECT 
    ENAME, 
    COUNT(*) AS CountOf
   FROM
    EMP_A
   GROUP BY ENAME
   HAVING COUNT(*)>1
  )
ORDER BY ENAME

F.

Posted 10-Sep-15 22:01pm

FrankNight

Solution 5

Here is a way of getting a list of Employees with the SAME (not similar) name.

An alternative to the other solutions using the Over clause with the Row_Number.
This one uses the Count aggregate function on the Over clause.

SQL

with Emp as (
--test dummy data
	select 
		'FARAH' ename, 
		'RESEARCH' dept_name, 
		'ANALYST' designation, 
		30000 salary,
		convert(datetime, 'Dec 03, 1991') date_of_join
	union all select 'TOMAR', 'SALES', 'SALESMAN', 15000, convert(datetime, 'Sep 08, 2001')
	union all select 'SAXENA', 'SALES', 'SALESMAN', 12500, convert(datetime, 'Sep 28, 1999')
	union all select 'TOMAR', 'SALES', 'SALESMAN', 14500, convert(datetime, 'Feb 22, 1997')
	union all select 'ANAND', 'RESEARCH', 'CLERK', 11000, convert(datetime, 'Jan 12, 1993')

	--addition emp
	--union all select 'TOMAR', 'SALES', 'SALESMAN', 15500, convert(datetime, 'Feb 22, 2010')
	--union all select 'FARAH', 'RESEARCH', 'ANALYST', 33000, convert(datetime, 'Sep 28, 2010')
)
select * 
from (
		select
			count(*) over (partition by e.ename) SameNameCnt,
			*
		from Emp e
	) EmpWithNamCnt 
where EmpWithNamCnt.SameNameCnt > 1
order by 
	ename,
	date_of_join
;

Hope that helps out :)

OVER Clause: https://msdn.microsoft.com/en-gb/library/ms189461.aspx[^]

Posted 14-Sep-15 2:17am

jaket-cp

Solution 2

SELECT ENAME, DESIGNATION, DEPT_NAME, DATE_OF_JOIN
FROM EMP_A
GROUP BY ENAME;

Posted 10-Sep-15 2:48am

Member 11732163

Comments

Bittu14 10-Sep-15 9:20am

It has shown the error - "ORA-00979: not a GROUP BY expression".

Schatak 11-Sep-15 1:58am

You can not make group by on one column while selecting more than one column in Select statement so your query will not work.

Bittu14 11-Sep-15 4:20am

ok. Thanks for your information.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Uttam katariya · Accepted Answer · 2015-09-10T02:48:00

Solution 1

Hi for your solution create temp table and insert record like below query

SQL

select Name,ROW_NUMBER() OVER( PARTITION BY base.Name order by name) AS duplicate
 from EMP_A AS base
where FirstName = '10Feb2015'

and after search from main table with join of temp table like in join clause
base.name = temp.main and duplicate = 2

by using this step i hope you get your solution
thanks
Uttam katariya

Posted 10-Sep-15 2:48am

Uttam katariya

Comments

Bittu14 10-Sep-15 9:28am

How I will put the name over there? Assume I am not able to see the table then how i will find that duplicate value?

Bittu14 10-Sep-15 9:41am

If i will give the name over there then also the DUPLICATE variable having some problem.

Uttam katariya 11-Sep-15 0:07am

Hi can you gice your query example so i can help you?

Bittu14 11-Sep-15 4:11am

suppose, I do not know what are the values has stored in the table. I need to find the duplicate values from the table, based on the ENAME column.

So, how I will find it, that is my simple question.

Schatak · Accepted Answer · 2015-09-10T20:14:00

Solution 3

As per solution #2
try this

SQL

Select  e.ENAME, DESIGNATION, DEPT, DATEOFJOIN from Employee e ,
(
    SELECT eName,ROW_NUMBER() OVER( PARTITION BY ename order by ename) AS duplicate
        from employee
    ) AS  temp
 WHERE temp.ENAME = e.Ename and duplicate = 2

Posted 10-Sep-15 20:14pm

Schatak

Comments

Bittu14 11-Sep-15 4:18am

Thank you so much Schatak.