CODEWITHSUNDEEP
sqlsql-servert-sql
Can
Can

Reputation: 9

How can I get record which has same value in two column?

In this table I have 3 column firstname lastname and age. In this table there are some duplicate record. For example In two record, firstname and lastname are same only age different. I want to get which records like that with SQL query. How can I get records which has same first name and same last name.

Sample data

Sample Table

I want to get all record which has john as first name and has doe as lastname.

Upvotes: 0

Views: 1535

Answers (5)

Bold
Bold

Reputation: 283

SELECT FirstName, LastName, COUNT(*)
FROM Table
GROUP BY FirstName
HAVING (COUNT(*) > 1);

This query will return data that has duplicates and show the total duplicates

Upvotes: 0

Akash R
Akash R

Reputation: 129

select user1.*     
         from User user1 , User user2
             where user1.firstName = user2.firstName 
                   and user1.lastName = user2.lastName 
                   and user1.id != user2.id

Upvotes: 0

DhruvJoshi
DhruvJoshi

Reputation: 17126

Here's a query which gives you all records which have same name but diff or same age

Select *, 
   rn =dense_rank() 
        over (partition by firstname, lastname order by age asc) 
from yourtable

This gives same rank(rn column) value for all records with same name. Now if you need all records which have same name from the table.

select * from 
(
    Select *, 
       rn =dense_rank() 
            over (partition by firstname, lastname order by age asc) 
    from yourtable
) T
where firstname =@firstname and lastname =@lastname

Upvotes: 1

Bharat Vishe
Bharat Vishe

Reputation: 303

You can use nested query and count function here

Assuming table name is users

select * from users 
    where (select count(id) from users as nested_users 
            where nested_users.FirstName=users.FirstName 
            and nested_users.LastName=users.LastName) > 1;

nested_users is an alias

Upvotes: 0

allmhuran
allmhuran

Reputation: 4454

One way which is quite "natural" to read is to ask, "for all rows, is there another row (ie, a row with a different id) that has the same first and last name":

select t.id, t.firstName, t.lastName, t.age
from   MyTable t
where  exists  
       (
          select *
          from   MyTable
          where  firstName = t.firstName
                 and lastName = t.lastName
                 and id != t.id
       )

Upvotes: 1

Related Questions