SQLite query with GROUP BY and CASE clause-CodePudding

In a SQLite DB I have the following data structure in the table items (simplified for sake of clarity):

id	storeId	archived
1	a-1	0
2	a-1	1
3	a-1	1
4	b-1	0
5	b-1	0
6	b-1	0
7	b-1	0
8	b-1	1

I'd like to select all items that have been archived (archived = 1), and group by storeId those that have not been archived.

To do so, I've written this query:

const queryString = `
  SELECT *
  FROM items
  GROUP BY 
    CASE
      WHEN items.archived = 1  
        THEN items.id
      ELSE IFNULL(storeId, id)
    END
`;

My issue is that the non archived items with a storeId for which there are archived items are never displayed. They seem to be grouped with the archived items with the same storeId value.

In other terms, with that query and that data, I only get the following:

id	storeId	archived
2	a-1	1
3	a-1	1
8	b-1	1

I guess I am missing something of how the GROUP BY works. How can I achieve my intended result?

CodePudding user response：

For anyone interested, the answer was grouping by multiple columns, with concatenation:

const queryString = `
  SELECT items.*, items.storeId || ' ' || items.archived AS concatenatedValues
  FROM items
  GROUP BY 
    CASE
      WHEN items.archived = 1 THEN items.id
      ELSE concatenatedValues
    END
`;

CodePudding user response：

If what you want is the row with the min id for the storeIds with archived = 0, then you can do it with MIN() window function in a CASE expression:

SELECT DISTINCT 
       CASE 
         WHEN archived THEN id
         ELSE MIN(id) OVER (PARTITION BY storeId)
       END AS id,
       storeId,
       archived
FROM items;

Or, if archived = 0 (if it exists) is not always the first in each storeId, use FIRST_VALUE() window function:

SELECT DISTINCT 
       CASE 
         WHEN archived THEN id
         ELSE FIRST_VALUE(id) OVER (PARTITION BY storeId, archived ORDER BY id)
       END AS id,
       storeId,
       archived
FROM items;

See the demo.