Select first row of a particular group with one attribute selected as 0 while all other rows should be selected as is

Question

I have a requirement where I need to select first row of a particular group and for all other groups I need to select the row as is in the same query.

Here is my table

CREATE TABLE mytable(
   afid  INTEGER  NOT NULL
  ,cid   INTEGER  NOT NULL
  ,apid  INTEGER  NOT NULL
  ,pt    INTEGER  NOT NULL
  ,flags BIT  NOT NULL
  ,jid   INTEGER  NOT NULL,
  PRIMARY KEY (jid, afid, cid)
);
INSERT INTO mytable(afid,cid,apid,pt,flags,jid) VALUES (1,258963147,7,1693898626,0,991);
INSERT INTO mytable(afid,cid,apid,pt,flags,jid) VALUES (2,258963147,7,1693898626,0,991);
INSERT INTO mytable(afid,cid,apid,pt,flags,jid) VALUES (3,258963147,9,1693898626,1,995);
INSERT INTO mytable(afid,cid,apid,pt,flags,jid) VALUES (4,258963147,9,1693898626,1,995);
INSERT INTO mytable(afid,cid,apid,pt,flags,jid) VALUES (5,258963147,9,1693898626,1,995);
INSERT INTO mytable(afid,cid,apid,pt,flags,jid) VALUES (6,258963147,10,1693898626,1,998);
INSERT INTO mytable(afid,cid,apid,pt,flags,jid) VALUES (7,258963147,10,1693898626,1,998);
INSERT INTO mytable(afid,cid,apid,pt,flags,jid) VALUES (8,258963147,10,1693898626,1,998);
INSERT INTO mytable(afid,cid,apid,pt,flags,jid) VALUES (9,258963147,15,1693898626,0,999);
INSERT INTO mytable(afid,cid,apid,pt,flags,jid) VALUES (10,258963147,15,1693898626,0,999);

afid	cid	apid	pt	flags	jid
1	258963147	7	1693898626	0	991
2	258963147	7	1693898626	0	991
3	258963147	9	1693898626	1	995
4	258963147	9	1693898626	1	995
5	258963147	9	1693898626	1	995
6	258963147	10	1693898626	1	998
7	258963147	10	1693898626	1	998
8	258963147	10	1693898626	1	998
9	258963147	15	1693898626	0	999
10	258963147	15	1693898626	0	999

In the above table, I need to do make a select query such that, for rows where flag = 1 I get just the first rows for each group of jobId with the attribute value of afid as 0.

Expected output(see row no 3, 4):

afid	cid	apid	pt	flags	jid
1	258963147	7	1693898626	0	991
2	258963147	7	1693898626	0	991
0	258963147	9	1693898626	1	995
0	258963147	10	1693898626	1	998
9	258963147	15	1693898626	0	999
10	258963147	15	1693898626	0	999

I am currently doing the above by breaking/splitting my queries. Is it possible to do this in a single query?

Answer 1

I am currently doing the above by breaking/splitting my queries. Is it possible to do this in a single query?

Sure, you can use a window function in a subquery to order the rows within a grouping (PARTITION) and then only select the first rows when the flags field equals 1 (and then show 0 as the afid value) otherwise all rows when it equals 0.

Example query:

SELECT
    IIF(flags = 1, 0, afid) AS afid,       
    cid,
    apid,
    pt,
    flags,
    jid
FROM
(
    SELECT
        afid,
        cid,
        apid,
        pt,
        flags,
        jid,
        ROW_NUMBER() OVER (PARTITION BY apid ORDER BY afid) AS SortId
    FROM mytable
) AS mytable_sorted
WHERE (flags = 1 AND SortId = 1) -- Only the first row where flag = 1
    OR flags = 0; -- Or all rows when flag = 0;

Other ways this can be implemented is with a CTE, or a UNION ALL clause instead (which may be more performant than the OR clause). But not sure if you'd consider a UNION ALL as a single query (it technically is, with multiple parts to it).