|
View previous topic :: View next topic
|
| Author |
Message |
jedidiah_raj
New User
Joined: 20 Jan 2007
Posts: 2
Location: India
|
|
|
|
Hi,
I want to assign numbers to each row retrieved from the query. If i would say select row number, emp id, emp name from employees I would want db2 to return
1 626 John
2 678 David
3 999 Brad
is there a way I could do this in DB2
Thanks much! |
|
| Back to top |
|
 |
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| What version of DB2 do you have installed? |
|
| Back to top |
|
|
jedidiah_raj
New User
Joined: 20 Jan 2007
Posts: 2
Location: India
|
|
|
|
| Akatsukami wrote: |
| What version of DB2 do you have installed? |
9 |
|
| Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| Is a column in a table which will be included in your SELECT statemeny given the data type ROWID? |
|
| Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
Addendum: if not, and if the row number can be volatile across REORGs, you can use:
| Code: |
| SELECT RID(EMPLOYEES) AS ROW_NUMBER, EMP_ID, EMP_NAME FROM EMPLOYEES |
|
|
| Back to top |
|
|
agkshirsagar
Active Member
Joined: 27 Feb 2007
Posts: 691
Location: Earth
|
|
|
|
@OP,
If I understood your question correctly, this should do the trick for you.
| Code: |
SELECT ROW_NUMBER() OVER(ORDER BY EMP_ID) AS ROW_NUM,
EMP_ID,
EMP_NAME FROM EMPLOYEES; |
IMHO, this trick has no practical value in application programming. Was this an interview question? |
|
| Back to top |
|
|
karthikr44
Active User
Joined: 25 Aug 2007
Posts: 235
Location: Chennai
|
|
|
|
HI,
You can also use CREATE SEQUENCE feauture..
Please see the below example...
| Code: |
CREATE SEQUENCE MYSEQ
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO CYCLE
CACHE 24;
SELECT NEXT VALUE FOR MYSEQ AS SEQ,
row number, emp id, emp name
from employees I;
|
Try it..
Regards
R KARTHIK |
|
| Back to top |
|
|
agkshirsagar
Active Member
Joined: 27 Feb 2007
Posts: 691
Location: Earth
|
|
|
|
Karthik's solution removes the ORDER BY overhead present in my query.
If the order is not important, please use-
| Code: |
SELECT ROW_NUMBER() OVER() AS ROW_NUM,
EMP_ID,
EMP_NAME FROM EMPLOYEES; |
|
|
| Back to top |
|
|
|
|
