Select DISTINCT name,id,status from names where status = 1

*Michele Pradella*
/R&D Software Engineer
/ <>
Office: +39 0375 889091

Selea s.r.l

V. Aldo Moro, 69
46019 Cicognara MN - Italy
Phone: +390375889091
Fax: +390375889080


Note: The information contained in this message may be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you.

Il 29/06/2018 17.50, Paul Sanderson ha scritto:
I have a table

Create table names (id int, status int, name text)

1, 1, 'paul'

2, 1,'helen'

3, 0, 'steve'

4, 0, 'steve'

5, 0, 'pete'

6, 0, 'paul'

I want a query that returns all of the records with status = 1 and unique
records, based on name, where the status =0 and the name is not in the list

So from the above I would want to see

1, 1, paul

2, 1, helen

3, 0, steve (or 4, 0, steve)

5, 0, pete

I could do something like

Select * from names where status = 1 or name not in (select name from names
where status = 1)

But this gets both rows for steve, e.g.

1, 1, paul

2, 1, helen

3, 0, steve

4, 0, steve

5, 0, pete

while I am not bothered about which of the two steves I get back, I must
have all occurences of names with status = 1

I am probably missing somethng obvious

SQLite Forensics Book <>
sqlite-users mailing list

sqlite-users mailing list

Reply via email to