CODEWITHSUNDEEP
sqlduplicates
yturgun
yturgun

Reputation: 303

SQL - Remove the duplicate Results

I have a table that looks like this:

name     | surname
------------------
John     |  John
Jessica  |  Madson

I have a query like this:

SELECT *
FROM TABLE
WHERE name LIKE '%j%'
    OR surname LIKE '%j%'

What I get:

John John
John John
Jessica Madson

What I want:

John John
Jessica Madson

How can I get rid of the duplicate results?

Upvotes: 13

Views: 65029

Answers (4)

Suraj Kumar
Suraj Kumar

Reputation: 5653

You can use the DISTINCT in SQL Server to get the distinct records only. Apart from this you can also use the ROW_NUMBER (Transact-SQL) function to get the distinct result by assigning the numbers to a result set.

The syntax of row_number() is as shown below.

ROW_NUMBER ( )   
    OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

Below is the implementation.

Create table TempTable (name varchar(50), surname varchar(50))

Insert into TempTable Values 
               ('John', 'John')
             , ('John', 'John') 
             , ('Jessica', 'Madson')
             , ('Jessica', 'Madson') 
             , ('Suraj', 'Kumar')
             , ('Peter', 'Smith')

Select * from TempTable -- All records.

Select * from(
SELECT name
       ,surname
       ,row_number() over (partition by name, surname order by name, surname) as RN -- For partition
FROM TempTable
WHERE name LIKE '%j%' OR surname LIKE '%j%'
)temp
where RN = 1 -- For Distinct records only

The output will look like as shown below:

enter image description here

You can find the demo at - db<>fiddle

Upvotes: 3

zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
zzzzzzzzzzzzzzzzzzzzzzzzzzzzzz

Reputation: 5759

You could also use group by

SELECT name, surname
FROM yourtable
WHERE name LIKE '%j%' OR surname LIKE '%j%'
GROUP BY name, surname

Upvotes: 3

Orbiting Eden
Orbiting Eden

Reputation: 1512

Try: 

SELECT DISTINCT name, surname FROM table WHERE name LIKE '%j%' OR surname LIKE '%j%'

Upvotes: 7

Mark Byers
Mark Byers

Reputation: 839114

Use DISTINCT:

SELECT DISTINCT name, surname
FROM yourtable
WHERE name LIKE '%j%' OR surname LIKE '%j%'

Upvotes: 26

Related Questions