Adding Counter inside a select statement [message #21140] |
Mon, 15 July 2002 22:15 |
George V Thomas
Messages: 2 Registered: July 2002
|
Junior Member |
|
|
Hi All,
I got a table X with fields
DateCreated Id
12-Jun-2002 101
14-Jun-2002 102
12-Jun-2002 102
11-Jun-2002 101
15-Jun-2002 103
If i use a select Statement like
SELECT DateCreated,ID, ROWNUM From X Order By DateCreated, Then the result will be
DateCreated Id RowNum
15-Jun-2002 103 5
14-Jun-2002 102 2
12-Jun-2002 102 3
12-Jun-2002 101 1
11-Jun-2002 101 4
my question is that is there any way that i can insert counter like Cont inside the SELECT statement so that my Result will be like
DateCreated Id RowNum Cont
15-Jun-2002 103 5 1
14-Jun-2002 102 2 2
12-Jun-2002 102 3 3
12-Jun-2002 101 1 4
11-Jun-2002 101 4 5
Can We do that? or any other Method?
George
|
|
|
|
Re: Adding Counter inside a select statement [message #21157 is a reply to message #21140] |
Tue, 16 July 2002 10:14 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
As mentioned in a response in another forum:
sql>select datecreated, id, row_number() over (order by datecreated desc, id desc) counter
2 from t3;
DATECREATED ID COUNTER
------------------- --------- ---------
06/15/2002 00:00:00 103 1
06/14/2002 00:00:00 102 2
06/12/2002 00:00:00 102 3
06/12/2002 00:00:00 101 4
06/11/2002 00:00:00 101 5
|
|
|