1

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?

1 Answer 1

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.