Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2006

    Unanswered: Calculating row number using correlated subquery

    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,
    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,

  2. #2
    Join Date
    Jun 2006

    Found the solution using different method

    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.


  3. #3
    Join Date
    Dec 2007
    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..
    Steven Green - Myrtle Beach, South Carolina USA

    Oasis Trading Post
    - Collectibles and Memorabilia
    - Vintage Lego Sets and Supplies
    - and Paradox Support, too

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts