Re: [sqlite] unique values from a subset of data based on two fields

Note that this is SQLite3 specific (and specific to Sybase of the era where Microsoft SQL Server was actually just a rebranded Sybase, and Microsoft re-writes of SQL Server up to about 2000). Technically you cannot do a query of the form: SELECT c1, c2 FROM t1 GROUP BY c2; because each

Re: [sqlite] unique values from a subset of data based on two fields

Easier and pretty obvious :) Thanks Keith Paul www.sandersonforensics.com SQLite Forensics Book On 29 June 2018 at 23:20, Keith Medcalf wrote: > >I want a query that returns all of the records with status = 1 and > >unique records, based on name,

Re: [sqlite] unique values from a subset of data based on two fields

>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 Translation into SQL using English to SQL Translator, using the most direct translation on the "problem statement" above directly

Re: [sqlite] unique values from a subset of data based on two fields

Thanks Ryan As often is the case the the actual problem is more complex than my example - sometimes we over simplify to, well, simplify - but you have both given me some ideas and I'll go away and play. Paul Paul www.sandersonforensics.com SQLite Forensics Book

Re: [sqlite] unique values from a subset of data based on two fields

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

Re: [sqlite] unique values from a subset of data based on two fields

Thank You David - I was just starting to play with CTEs Paul www.sandersonforensics.com SQLite Forensics Book On 29 June 2018 at 17:45, David Raymond wrote: > with status_one as ( > select * > from names > where status = 1 > ), > one_names

Re: [sqlite] unique values from a subset of data based on two fields

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-

Re: [sqlite] unique values from a subset of data based on two fields

Select DISTINCT name,id,status from names where status = 1 *Michele Pradella* /R Software Engineer / michele.prade...@selea.com Office: +39 0375 889091