2

I am trying to declare variables in my view, which i am finding out is not allowed. I need to be able to set each row a specific number based on an ID field. So if an ID field shows up 3 times it should count each Row as (1, 2, 3) and then when the ID changes it should restart the row# to 1.

What i have tried:

I have tried to add the variables in the view but this is not accepted when creating a view. 

    select  
    @Row_Number := case when @AppID = `afpd1`.`AppsID` then @Row_Number + 1 
    else 1 end as num, 
    @AppID := `afpd1`.`AppsID`,
    `afpd1`.`AppsID` AS `AppID1`,`afpd1`.`Amount`
    FROM
    (
        ( `app_fee_pay_data` `afpd1` JOIN `tracking` `tra1` ON ( ( `afpd1`.`AppsID` = `tra1`.`tci_application_id` ) ) )
        JOIN `fa_app_data` `fad1` ON ( ( `afpd1`.`AppsID` = `fad1`.`ApplicationID` )) join (select @Row_Number := 0) r
    ) 
    WHERE
    ( `tra1`.`tci_fee_submitted` = 11 and  `afpd1`.`TxnTypeId` = 2)

The above code gives me the desired results, but i need a work around so i can save this view and use it.

Ezzy
  • 79
  • 1
  • 8
  • Are you using MySQL 8.0? This sounds like the right application for `ROW_NUMBER() OVER (PARTITION BY AppsId)` – Bill Karwin Jan 14 '19 at 19:13
  • It is mysql but the work env. is Navicat. When i look for the function Row_Number() it is not showing when i type it in to my view? @BillKarwin – Ezzy Jan 14 '19 at 19:15
  • @BillKarwin do you know MySQL in Navicat does not support Row_Number()? – Ezzy Jan 14 '19 at 20:42
  • I don't use Navicat, so I don't know about that. The window functions are not implemented in MySQL before 8.0.2. You need to confirm the version of MySQL: `SELECT VERSION();` – Bill Karwin Jan 14 '19 at 21:21
  • 10.1.37-MariaDB @BillKarwin – Ezzy Jan 14 '19 at 21:51
  • In MariaDB, window functions were introduced in 10.2.0. https://mariadb.com/kb/en/library/window-functions/ – Bill Karwin Jan 14 '19 at 21:54
  • We need to stop calling MariaDB a MySQL-compatible database. It's a different product, with different versions, different features, etc. It has forked and will continue to diverge further and further away from MySQL. – Bill Karwin Jan 14 '19 at 21:55
  • thats what i was seeing, but they do call themselves a MySQL. do you know anything about MariaDb @BillKarwin – Ezzy Jan 14 '19 at 22:12
  • I don't use MariaDB. – Bill Karwin Jan 14 '19 at 22:17

0 Answers0