[SOLVED] Problem Using ROW_NUMBER() function in MariaDB

Problem Using ROW_NUMBER() function in MariaDB

I would like to have a row number column in a select table output, but when I try using the ROW_NUMBER() function MariaDB throws a syntax error. There are several references on the web (http://www.mysqltutorial.org/mysql-window-functions/mysql-row_number-function/ ) but so far I have not been successful. Here is a segment of my MariaDB table:

+---------------------+------------+  
| date_reading        | temp_patio |
|---------------------+------------+  
| 2019-09-03 06:26:00 |       17.6 |  
| 2019-09-03 06:33:00 |       17.5 |  
| 2019-09-03 06:40:00 |       17.5 |  
| 2019-09-03 06:46:00 |       17.5 |  
| 2019-09-03 06:53:00 |       17.4 |  
| 2019-09-03 07:00:00 |       17.4 |  
| 2019-09-03 07:07:00 |       17.4 |  
| 2019-09-03 07:13:00 |       17.4 |

The document says that the options for the "OVER ()" option are optional, but I have tried both with and without an OVER () clause and with and without an ORDER BY clause.

Here is my select command:

select ROW_NUMBER() OVER ( ) as Therow, * from MyData where Date_Reading > Now()- INTERVAL 3 HOUR;

Optionally I have tried without the OVER () clause and also using OVER ( ORDER BY ID).

My MariaDB version is

Server version: 10.1.38-MariaDB-0+deb9u1 Raspbian 9.0

Can someone assist?...RDK

Solution

Window functions are supported in MariaDB 10.2 or higher version only.

MariaDB 10.2 or higher:

SELECT 
    MyData.*,
    ROW_NUMBER() OVER ( ORDER BY ID ) as Therow
FROM MyData 
WHERE Date_Reading > Now()- INTERVAL 3 HOUR;

For lower version:

We can use the MySQL variable to do this job.

SELECT 
    MyData.*, 
    @row_num:= @row_num + 1 AS Therow
FROM 
    MyData, 
    (SELECT @row_num:= 0 AS num) AS c
WHERE Date_Reading > Now()- INTERVAL 3 HOUR
ORDER BY test.`date` ASC;