Oracle – Increment Variable/Counter in PL/SQL Select result

oracle

A rather silly question. I have a Oracle Db with Products and now read out the first 10 Products.

I now want the following display

1 Product A
2 Product XY
3 Product B

Stupid questions, but how to get the counter in front? I obviously have to increment, but I don't understand how that works. I also thought to work with WITH and tmp table, but can not figure out how that needs to be set up. 

SELECT POS ???, PRODUCTNAME FROM TBLPRODUCT

I am not very familiar with PL/SQL. Can someone give me a hint? Thanks so much.

Best Answer

ROWNUM is one approach as shown by Bob, but if you are using more complicated queries -- especially if you are ordering the rows explicitly -- it may not give the results you want.

Nowadays, analytic functions are generally a better approach as you have explicit control over the ordering:

SELECT ROW_NUMBER() OVER (ORDER BY productname), productname
  FROM tableproduct
  ORDER BY productname

Note that the ordering of the rows which determines the row numbers is separate from the ordering of the overall result set. In this example I've used the same ordering as that's what you're likely to want, but it's worth noting that this gives you more flexibility.

(And apologies for being a little pedantic, but this has nothing to do with PL/SQL, which is the procedural language embedded in Oracle. This is simply about Oracle's implementation of SQL.)

Related Topic