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)
If you'd like to number each row in a result set, SQL provides the ROW_NUMBER() function. This function is used in a SELECT clause with other columns. After the ROW_NUMBER() clause, we call the OVER() function. If you pass in any arguments to OVER , the numbering of rows will not be sorted according to any column.
ROW_NUMBER function is a SQL ranking function that assigns a sequential rank number to each new record in a partition. When the SQL Server ROW NUMBER function detects two identical values in the same partition, it assigns different rank numbers to both.
Getting a row number is easy—just find the cell you're interested in, click on it, and look at the highlighted row number on the side of the window. Sometimes, however, you need to use that information in a function. That's where ROW comes in.
MySQL ROW_NUMBER() Function. The ROW_NUMBER() function in MySQL is used to returns the sequential number for each row within its partition. It is a kind of window function. The row number starts from 1 to the number of rows present in the partition.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With