forum

Home / DeveloperSection / Forums / compare two fields of a single table in oracle sql

Ask Question

compare two fields of a single table in oracle sql

Mark Devid202309-May-2013
Hi All!

I'm having a table like below, I need to write a sql query to compare and validate the records are in proper order. ex: US is having no1 in column2 like that each 

country have their own no's , I need to write a code to validate that no country has the same no's assigned for more than one country and need to check weather the country has his assigned no only.

My table structure as per following

|column1|column2|
--------------------------------
|US  |no1    |
|Japan|no2    |
|dravid |no3|

Please help me as soon as possible.

mssql servermssql server 
Updated on 09-May-2013
Mark Devid

Mark Devid

Follow

Can you answer this question?

Answer

1 Answers

AVADHESH PATEL

AVADHESH PATEL

09-May-2013
Hi Mark!

Try as following

SELECT 
    column2, COUNT(*) TotalCount
FROM YourTable
GROUP BY column2
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
where TotalCount returns the count of number assigned multiple times. or

select * From(    
  SELECT distinct Column1, Column2, COUNT(*) over (partition by Column2) TotalCount
  FROM YourTable
)x 
where TotalCount>1

I hope it resolve your problem

Liked By