CODEWITHSUNDEEP
sqlsqlite
adayzdone
adayzdone

Reputation: 11238

Find unique values from records that contain duplicate values in a different field

I am trying to return all value of the_id that have been received within the past 8 days and there is at least 1 duplicated name. This should return 1,5.

MYTABLE

SELECT the_id, count(*) c 
FROM "MYTABLE" 
WHERE "received" < date('now','-8 days') 
GROUP BY the_name HAVING c > 1;

Upvotes: 0

Views: 29

Answers (2)

surfmuggle
surfmuggle

Reputation: 5942

From database.guide i found this sample

WITH cte AS 
    (
        SELECT 
            *, 
            ROW_NUMBER() OVER ( 
                PARTITION BY PetId, PetName, PetType 
                ORDER BY PetId, PetName, PetType
                ) AS Row_Number
        FROM Pets
    )
SELECT * FROM cte WHERE Row_Number <> 1;

The above returns this result for the provided sample records (see below):

enter image description here

Sample records and demo

CREATE TABLE Pets (
    PetId Integer,
    PetName Text,
    PetType Text
);
INSERT INTO Pets (PetId, PetName, PetType) 
VALUES 
  (1, 'Wag', 'Dog'),
  (1, 'Wag', 'Dog'),
  (2, 'Scratch','Cat'),    
  (3, 'Tweet','Bird'),
  (4,  'Bark','Dog'),
  (4,  'Bark','Dog'),
  (4,  'Bark','Dog'),
  (4,  'Bark','Dog');

Upvotes: 0

NoChance
NoChance

Reputation: 5752

This may work for you:

SELECT a.the_id
FROM   "MYTABLE" A, "MYTABLE" B
WHERE  (a.the_name = b.the_name)
       AND 
       (a."received" < date('now','-8 days') 
        AND b."received" < date('now','-8 days')
       )
Group by a.the_id 
having count(*)>1

Upvotes: 1

Related Questions