sql - Simplifying UNION query to use a single SELECT -

the data:

i have sqlite table following example rows:

+-------------+----------------+-----------------+ | id          | groupid        | selected        | +-------------+----------------+-----------------+ |           1 | -1             | 0               | |           2 | 2              | 0               | |           3 | 2              | 1               | |           4 | 5              | 0               | +-------------+----------------+-----------------+ 

it's pretty simple, if groupid less 0, it's single element, else it's in group (even if groupid not referencing of ids.

the query:

i want filter every element groupid less 0 , not selected plus every first element each group. table, ids: 1, 2 , 4.

the current solution union:

select * (select * elements (groupid < 0 , selected = 0 ) union select * elements groupid >= 0 group groupid)

the goal:

i want change query use single select statement. single statement can use anything, aggregate functions.

thanks in advance answers!

there no need union, can done in single clause. here query return list of ids requested.

select  min_id    (         select  groupid                 ,min(id) min_id            elements           (groupid < 0 , selected = 0)         or      groupid >= 0         group                 groupid         ) 

here second query based on comments below.

select  groupid         ,min(id) min_id    elements   (groupid < 0 , selected = 0) or      groupid >= 0         group         groupid