CODEWITHSUNDEEP
mysqlsqluser-variables
Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

simulate row_number with variables ignore order

I was trying to help with this question Selecting rows based on a few rules

The idea is for each user select the row id with the language matching @lenguage otherwise select the first language created.

Because doesn't have row_number(), had to use user variables. Consider I add a field test_id so could put all the cases on the same table.

SQL DEMO

  SELECT t.* , (`language` = @language) as tt,
         @rn := if (@partition = CONCAT(`test_id`, '-', `user`),
                    @rn + 1,
                    if(@partition := CONCAT(`test_id`, '-', `user`), 1, 1)
                   ) as rn,
         @partition           
  FROM Table1 t
  CROSS JOIN ( SELECT @language := 'de', @rn := 0, @partition := '' ) as var
  ORDER BY CONCAT(`test_id`, '-', `user`), 
           (`language` = @language) DESC,
           `created`

OUTPUT

But even when the ORDER BY give the correct sort, partitions 1-4 and 3-4 don't put the language 'de' as first. So something is altering how the variable @rn is being assigned.

| test_id | id | title | language |              created | user | tt | rn | @partition |
|---------|----|-------|----------|----------------------|------|----|----|------------|
|       1 |  3 |     c |       de | 2019-01-03T00:00:00Z |    4 |  1 |  3*|        1-4 |
|       1 |  1 |     a |       en | 2019-01-01T00:00:00Z |    4 |  0 |  1 |        1-4 |
|       1 |  2 |     b |       es | 2019-01-02T00:00:00Z |    4 |  0 |  2 |        1-4 |
|       2 |  1 |     a |       en | 2019-01-01T00:00:00Z |    4 |  0 |  1 |        2-4 |
|       2 |  2 |     b |       es | 2019-01-02T00:00:00Z |    4 |  0 |  2 |        2-4 |
|       3 |  1 |     a |       en | 2019-01-01T00:00:00Z |    3 |  0 |  1 |        3-3 |
|       3 |  3 |     b |       de | 2019-01-03T00:00:00Z |    4 |  1 |  2*|        3-4 |
|       3 |  2 |     b |       es | 2019-01-02T00:00:00Z |    4 |  0 |  1 |        3-4 |
|       3 |  4 |     c |       de | 2019-01-04T00:00:00Z |    5 |  1 |  1 |        3-5 |

Upvotes: 1

Views: 114

Answers (1)

Barmar
Barmar

Reputation: 782498

Ordering is done after all the rows are selected. Your @rn variable is being set during the row selection, so it's using the internal order of rows, not the order specified in your ORDER BY clause.

You need to move the ordering into a subquery, then calculate @rn in the main query.

  SELECT t.*,
         @rn := if (@partition = CONCAT(`test_id`, '-', `user`),
                    @rn + 1,
                    if(@partition := CONCAT(`test_id`, '-', `user`), 1, 1)
                   ) as rn,
         @partition           
  FROM (
    SELECT *, (language = @language) AS tt
    FROM Table
    CROSS JOIN (SELECT @language := 'de') AS var
    ORDER BY CONCAT(test_id, '-', user),
            tt DESC,
            created
  ) AS t
  CROSS JOIN ( SELECT @rn := 0, @partition := '' ) as var

Upvotes: 2

Related Questions