CODEWITHSUNDEEP
sqldb2
Abruzzo Forte e Gentile
Abruzzo Forte e Gentile

Reputation: 14869

query for roww returning the first element of a group in db2

Suppose I have a table filled with the data below, what SQL function or query I should use in db2 to retrieve all rows having the FIRST field FLD_A with value A, the FIRST field FLD_A with value B..and so on?

  ID   FLD_A    FLD_B
  1    A        10
  2    A        20
  3    A        30
  4    B        10
  5    A        20
  6    C        30

I am expecting a table like below; I am aware of grouping done by function GROUP BY but how can I limit the query to return the very first of each group?

Essentially I would like to have the information about the very first row where a new value for FLD_A is appearing for the first time?

  ID   FLD_A    FLD_B
  1    A        10
  4    B        10
  6    C        30

Upvotes: 0

Views: 452

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

A good way to approach this problem is with window functions and row_number() in particular:

select t.*
from (select t.*,
             row_number() over (partition by fld_a order by id) as seqnum
      from table1
     ) t
where seqnum = 1;

(This is assuming that "first" means "minimum id".)

If you use t.*, this will add one extra column to the output. You can just list the columns you want to avoid this.

Upvotes: 1

bvr
bvr

Reputation: 4826

Try this it works in sql

SELECT * FROM Table1 
WHERE ID IN (SELECT MIN(ID) FROM Table1 GROUP BY FLD_A)

Upvotes: 1

Related Questions