Hello All,

I want to generate row numbers on a Paradox table.
The following code works on my SQL server but not on Paradox.

SELECT TT.Hours,

(SELECT COUNT(*)
FROM "TopTenTable.DB" TT2
WHERE TT2.Hours <= TT.Hours
) As Num

FROM "TopTenTable.DB" TT
ORDER BY TT.Hours

The values for 'Num' are zero (0).
Does anyone know if Paradox Local SQL has a problem with correlated sub-queries of this type.

Any other suggestions of how I might calculate the row number would also do the trick, using LocalSQL or QBE. I'm not fussy.

Thanks in advance,
John

OK, I managed to find a solution on the forums here that will do the trick.
(It's always AFTER you've asked the question that you find the answer, I don't know why.)

SELECT TT.Hours, TT.Class, COUNT(*) AS RowNum
FROM "TopTenTable.DB" TT
JOIN "TopTenTable.DB" TT2
ON TT2.Hours <= TT.Hours
GROUP BY TT.Hours, TT.Class
ORDER BY TT.Hours

This should do the trick for me.

If anyone has any idea as to why the correlated subquery didn't work I'd still really like to know.

Thanks,
John

I can't tell you if that is supported in Local SQL, but in native QBE it is not.. you would scan the table after the query to seed a "record number" field, if needed..