lklingaman
New Member
I have seen numerous variations of this question, but I am yet to see a solution. Hopefully someone can help me out!
My Setup:
Merant ODBC for Progress 9.1C
My Problem:
Acquiring a column in a select statement to display the current row number of the query from a table. (The date value will be unique in the table).
I know the below example is odd, but it does work on MS SQL and describes the problem.
i.e.
SELECT D1.DATEID, D1.EVENTDATE,
(SELECT COUNT(*)+1 AS ROWPOS FROM PUB.DATES AS D2
WHERE D2.EVENTDATE < D1.EVENTDATE) AS RowNum
FROM
PUB.DATES AS D1
ORDER BY D1.EVENTDATE
DateID EventDate RowNum
4321432 10/16/2006 1
6543533 10/17/2006 2
1234354 10/18/2006 3
Is there any way through Progress SQL to accomplish this? I cannot change the source table and I do not want to add the RowNum column through additional processing outside of the SQL command.
Thank you in advance,
Larry Klingaman
My Setup:
Merant ODBC for Progress 9.1C
My Problem:
Acquiring a column in a select statement to display the current row number of the query from a table. (The date value will be unique in the table).
I know the below example is odd, but it does work on MS SQL and describes the problem.
i.e.
SELECT D1.DATEID, D1.EVENTDATE,
(SELECT COUNT(*)+1 AS ROWPOS FROM PUB.DATES AS D2
WHERE D2.EVENTDATE < D1.EVENTDATE) AS RowNum
FROM
PUB.DATES AS D1
ORDER BY D1.EVENTDATE
DateID EventDate RowNum
4321432 10/16/2006 1
6543533 10/17/2006 2
1234354 10/18/2006 3
Is there any way through Progress SQL to accomplish this? I cannot change the source table and I do not want to add the RowNum column through additional processing outside of the SQL command.
Thank you in advance,
Larry Klingaman