What I'm trying to do is to filter by the clients that registered twice in the DB. This as I need to know who of them came at least twice, that is why I´m working with a table that registers every time they registered in the system as it follows:

In this case I want to delete David´s record, as I only want people who has order numbers distinct than "one".

I tried this SQL:

select * 
from table 
where order_number > 1

however what this does is remove all the rows of the first orders, including the ones that came back.

Does somebody know an easy way for me to compare row names and filter by that or just how could I delete those rows in which there are clients with only one entry?

CodePudding user response：

you need something like this :

select * from yourtable 
where not exists (select 1 from yourtable where order_number >1)

or:

select client 
from tablename
group by client
having count(*) > 1

CodePudding user response：

CREATE TABLE records (
ID INTEGER PRIMARY KEY,
order_number TEXT NOT NULL,
client TEXT NOT NULL,
date DateTime NOT NULL
);

INSERT INTO records VALUES (1,'ONE', 'Adrew', '01.01.1999');
INSERT INTO records VALUES (2, 'TWO','Adrew', '02.02.1999');
INSERT INTO records VALUES (3, 'THREE','Adrew', '03.03.1999');
INSERT INTO records VALUES (4, 'ONE', 'David', '01.01.1999');
INSERT INTO records VALUES (5, 'ONE','Marc', '01.01.1999');
INSERT INTO records VALUES (6, 'TWO','Marc', '01.03.1999');



DELETE FROM records WHERE ID in 
( 
SELECT COUNT(client) as numberofclient FROM records 
Group By client Having Count (client) = 1
);