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.
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 |
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