The solution from David works perfectly, just want to point out some CTE
things since you mention getting into it.
1st - A nice thing about CTE is that, in the case of a non-recursive CTE
(like this one), it can well be replaced by a simple sub-query, however,
the CTE can be referenced more than once, unlike a sub-query.
2nd - One can even use the CTE in other sub-queries.
With this in mind, here is another option for the query which is
functionally equivalent (i.e. it's not better, simply showing alternate
CTE use):
with status_one as (
select *
from names
where status = 1
)
select min(id), status, name
from names
where status = 0 and name not in (select name from status_one)
group by status, name
union all
select * from status_one
;
CTE capability is one of my favourite additions ever to SQLite (I may
have mentioned this before), so I hope you too find them useful and joyful.
Cheers,
Ryan
On 2018/06/29 6:45 PM, David Raymond wrote:
with status_one as (
select *
from names
where status = 1
),
one_names as (
select distinct name
from status_one
)
select min(id), status, name
from names
where status = 0
and name not in one_names
group by status, name
union all
select * from status_one;
-----Original Message-----
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Paul Sanderson
Sent: Friday, June 29, 2018 11:50 AM
To: General Discussion of SQLite Database
Subject: [sqlite] unique values from a subset of data based on two fields
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
status=1
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
Paul
www.sandersonforensics.com
SQLite Forensics Book <https://www.amazon.co.uk/dp/ASIN/1980293074>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
