returning select query with row count

Question

I am trying to return values from a query adding a column for the row position without adding an identity column. I don't want the absolute position in the table, but the position in the query result

Suppose I have a table like this

 My_TBL
 -----------------------
 FLD_A     FLD_B   FLD_C
 a         A       t
 b         B       t
 c         C       p
 d         D       p
 ..        ..

and the select query is

 select FLD_A,FLD_B from My_Tbl where FLD_C='p'

 FLD_A    FLD_B
 -----------------------
 c         C      
 d         D

What do I have in Db2 to add in my query to get each row counted in that output?

 POS       FLD_A    FLD_B
 -----------------------
 1         c         C      
 2         d         D

Check this http://stackoverflow.com/questions/1079480/how-to-determine-position-of-row-in-sql-result-set , I hope this will answer your question. — Torukmakto, Apr 15 '13 at 07:29

score 1 · Accepted Answer · 2013-04-15T12:53:14.300

1

Use row_number(). It will only count the rows that are actually returned.

select  row_number() over (order by FLD_A,FLD_B) as POS,
        FLD_A,
        FLD_B 
    from My_Tbl 
    where FLD_C='p'

edited Apr 15 '13 at 12:53

answered Apr 15 '13 at 12:48

returning select query with row count

1 Answers1