COMPARE TWO FIELDS OF A SINGLE TABLE IN ORACLE SQL

Mark Devid

Total Post:102

Points:714
Posted by  Mark Devid
 1256  View(s)
Ratings:
Rate this:
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.

  1. AVADHESH PATEL

    Post:604

    Points:4228
    Re: compare two fields of a single table in oracle sql

    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

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!