How to select unique rows with SQlite SELECT statement by comparing neighbouring rows-CodePudding

I have an sqlite table that can contain "almost" duplicate rows.

where the only difference when these "duplicates" exists is that one of the rows has a NULL value in Foreign Key column:-

a simplified version of the table is as follows:-

NAME       FK1         FK2           WATCH   
AAAAAAAA   NULL        10099999        1     
AAAAAAAA   8008        10099999        1
BBBBBBBB   1111        30088888        2 
CCCCCCCC   2222        75656463        1
DDDDDDDD   3333        34242434        2
GGGGGGGG   NULL        70099999        1     
GGGGGGGG   4224        70099999        1

In the above example the rows I require to select are

NAME       FK1         FK2           WATCH   
AAAAAAAA   8008        10099999        1
CCCCCCCC   2222        75656463        1
GGGGGGGG   4224        70099999        1

e.g. Any row where WATCH = 1 AND where "duplicate" FK2 rows exists I require where FK1 IS NOT NULL

There is another scenario though that the same SQL statement must handle

NAME       FK1         FK2           WATCH   
KKKKKKKK   NULL        87656463        1     
LLLLLLLL   5454        65453535        2
MMMMMMMM   NULL        48723747        1 
MMMMMMMM   9229        48723747        1
NNNNNNNN   2765        77000007        2
OOOOOOOO   NULL        83343343        1     
ZZZZZZZZ   8118        63737422        1

In the above example the rows I require to select are

NAME       FK1         FK2           WATCH     
KKKKKKKK   NULL        87656463        1 
MMMMMMMM   9229        48723747        1
OOOOOOOO   NULL        83343343        1     
ZZZZZZZZ   8118        63737422        1

e.g. Any row where WATCH = 1 AND where "duplicate" FK2 rows exists I require where FK1 IS NOT NULL unless theres ONLY the FK1 IS NULL then I require those rows

Is this possible to achieve in a single SQLite statement?

I've got this statement that seems to work

SELECT  T1.name, T1.fk1, T1.fk2 
FROM  my_table T1
WHERE T1.watch = 1
AND T1.fk1 IS NOT NULL
UNION 
SELECT  T2.name, T2.fk1, T2.fk2 
FROM  my_table T2
WHERE T2.watch = 1
AND T2.fk1 IS NULL
AND T2.fk2  NOT IN (SELECT T3.fk2 FROM  my_table T3 WHERE T3.watch = 1 AND T3.fk1 IS NOT NULL)

Is the best approach?

CodePudding user response：

You can do it with aggregation:

SELECT NAME, MAX(FK1) FK1, FK2, WATCH
FROM my_table
WHERE WATCH = 1
GROUP BY NAME;

When the aggregate function MAX() is used in such queries, SQLite returns the row that contains the max value returned.

See the demo.