CODEWITHSUNDEEP
sqloracle-databaseplsql
sachin rv
sachin rv

Reputation: 69

compare two fields of a single table in oracle sql

I have a table like this, I need to write a sql query to compare and validate the records are in proper order. ex: dhoni is having no1 in column2 like that each players have their own no's , I need to write a code to validate that no player has the same no's assigned for more than one player. and need to check weather the player has his assigned no only. 

+-------+-------+
|column1|column2|
+-------+-------+
|dhoni  |no1    |
|sachin |no2    |
|dravid |no3    |
|dhoni  |no1    |
+-------+-------+

Note: write a query to validate the table data ex: to check dhoni should always has to get no1 in column2, irrespective of duplicate records and order, like wise need to check for other players also, irrespective of no of data's present in table.. just need to validate the things..

Upvotes: 0

Views: 1009

Answers (2)

Jeffery Joe
Jeffery Joe

Reputation: 21

Find one user with more than one no.

select column1 from table group by column1 having count(*) > 1;

Find the same no with more then one user.

select column2 from table group by column2 having count(*) > 1;

Upvotes: 1

TechDo
TechDo

Reputation: 18659

Please try:

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

Upvotes: 0

Related Questions