1

I have a table tbl_entso_cdbf with some values as shown below. I want to perform some operations on this table. I do not know why this simple query fails to perform row_number() operation.

This is a query I am using:

SELECT
    ROW_NUMBER() OVER (PARTITION BY utc ORDER BY value) AS row_num,
    pk, area_in, area_out 
    from tbl_entso_cdbf;

It gives some wierd error to check the syntax near '(Partition by utc order by value) AS row_num, pk, area_in, area_out.... I digged a lot but I have no clue about this error (https://mariadb.com/kb/en/mariadb/row_number/) (http://www.c-sharpcorner.com/blogs/rownumber-function-with-partition-by-clause-in-sql-server1)(https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql). I need to perform further sql operations to filter a big table but couldn't move because this errors. 

 pk  |   Utc             |      date         |area_in|area_out|  value  | 
  -----------------------------------------------------------------------
   1 | 2015-12-05T03:00Z |2015-12-05 03:00:00| 275   |  40    |   320   |
   2 | 2015-12-05T03:00Z |2015-12-05 03:00:00| 40    |  275   |   0     |
   3 | 2015-12-06T03:00Z |2015-12-06 03:00:00| 100   |  175   |   550   |
   4 | 2015-12-06T03:00Z |2015-12-06 03:00:00| 175   |  100   |   0     |
   5 | 2015-11-04T03:00Z |2015-11-04 03:00:00| 280   |  310   |   0     |
   6 | 2015-11-04T03:00Z |2015-11-04 03:00:00| 310   |  280   |   0     |
   7 | 2016-09-19T00:00Z |2016-09-19 00:00:00| 187   |  292   |   45    |
   8 | 2016-09-19T00:00Z |2016-09-19 00:00:00| 292   |  187   |   0     |
halfer
  • 19,824
  • 17
  • 99
  • 186
mitedu
  • 49
  • 8
  • simple MySQL doenst support ROW_NUMBER() and OVER (PARTITION BY ..)... you can simulate ROW_NUMBER() by using user variables. – Raymond Nijland Aug 08 '17 at 12:16
  • @Raymond Nijland , Could you please give me some insights to solve this problem (https://stackoverflow.com/questions/45546344/delete-min-values-from-records-after-comparing-two-values-from-table-in-sql/45551008#45551008) Thank you. – mitedu Aug 08 '17 at 12:25
  • this should help https://stackoverflow.com/questions/1895110/row-number-in-mysql – Raymond Nijland Aug 08 '17 at 12:44

0 Answers0