'How to get ROW_NUMBER() in SQL?

I tried to get the row number using ROW_NUMBER() but it shows the following error:

can't format message 13:896 -- message file C:\WINDOWS\firebird.msg not found. Dynamic SQL Error. SQL error code = -104. Token unknown - line 2, column 66.

Here is my code:

SELECT  avg(CSIDTL.RATING) ,SVD.SVCADVISORNAME, ROW_NUMBER() OVER(ORDER BY avg(CSIDTL.RATING) )
        FROM T_APPT_BOOKING_MSTR MSTR ,T_APPT_CSI_SURVEY CSI,T_APPT_CSI_SURVEY_DTL CSIDTL,
        T_SVC_SVCADVISOR_MASTER SVD
        WHERE MSTR.APPTBKID = CSI.APPTBKID
        AND CSI.CSI_SURVERYID = CSIDTL.CSI_SURVERYID
        AND SVD.SVCADVISORID = MSTR.SVCADVISORID
        AND CSI.FEEDBACK_STATUS = 'Y'
        AND CSIDTL.question ='Service Advisor'
        GROUP BY SVD.SVCADVISORNAME
        ORDER by avg(CSIDTL.RATING)
sqlfirebird


Solution 1:[1]

The ROW_NUMBER() function was introduced with Firebird 3.0, released just few days ago. See release notes, chapter Window (Analytical) Functions for exact syntax. The error you get suggest you're using an older version of Firebird which doesn't have this feature.

Solution 2:[2]

I use this in Firebird 2.5

Reference: http://www.firebirdfaq.org/faq343/

SELECT rdb$get_context('USER_TRANSACTION', 'row#') as row_number, DUMMY, A.*
FROM your_table A
CROSS JOIN
(SELECT rdb$set_context('USER_TRANSACTION', 'row#',
COALESCE(CAST(rdb$get_context('USER_TRANSACTION', 'row#') AS INTEGER), 0) + 1) AS dummy
FROM rdb$database) dummy

Solution 3:[3]

Other alternative in Firebird 2.5 is use of generators

CREATE GENERATOR tmp$rn;
UPDATE my_table t SET t.id_field = (SELECT FIRST 1 NEXT VALUE FOR tmp$rn AS "row_number"
FROM my_table ORDER BY another_field1 DESC, another_field2 DESC);
DROP GENERATOR tmp$rn;

Solution 4:[4]

This may be helpful,

Reference Firebird 3.0 Language Reference - 10.4. Ranking Functions

  • Available in :DSQL, PSQL
  • Result type :BIGINT

Syntax

ROW_NUMBER () OVER <window-specification>

Returns the sequential row number in the partition of the result set, where 1 is the first row in each of the partitions.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 ain
Solution 2 Raul Salvatierra
Solution 3 vimuth
Solution 4 Fernando Murrieta

Related Questions