1

I have a problem with making the equivalent of row_number in mysql. Here is my code:

  SELECT
    @row_number:=CASE
        WHEN @facebookv = a.facebook THEN @row_number:= @row_number + 1
        ELSE @row_number:=1
    END AS num,
   @facebookv:=a.facebook as denumire_facebook,
    a.Keyword,
    sum(a.Sales) as Sales
FROM
    ams_prod a , (SELECT @row_number:=0,@facebookv='') AS t
    group by a.facebook,a.Keyword
    having sum(a.Sales)>0
ORDER BY a.facebook;

So the problem is that it is returning me the same "num" for the same "facebook" column value like:

enter image description here

And no, they are not doubled, they are just repeating a couple of times. Any idea why is this happening? Thanks!

Mara M
  • 153
  • 1
  • 1
  • 10
  • 1
    It is unclear what you are trying to do. One problem I can see is using `ORDER BY` in the same query block with variables being evaluated in the `SELECT` clause. Check this answer: https://stackoverflow.com/a/53465139/2469308 It should give your noteworthy pointers while approaching a query using variables. – Madhur Bhaiya Dec 10 '18 at 10:43
  • @MadhurBhaiya thanks for your answear. It helped me to understand how to use my query block. – Mara M Dec 10 '18 at 11:03
  • Use MySQL 8.0. It supports ROW_NUMBER() and other windows functions. – Øystein Grøvlen Dec 11 '18 at 00:56
  • @oysteing The answer that helped me is in this link stackoverflow.com/a/53465139/2469308 Also I can't upgrade the MySQL version because I work on someone else's server. – Mara M Dec 11 '18 at 07:55

0 Answers0