Аналог ROW_NUMBER в MySQL

В статье T-SQL: Функция ROW_NUMBER я рассказывал, как выводить номер строки в запросе в Microsoft SQL Server. Тут я расскажу, как выводить номер строки в запросе в MySQL.

Для того, что бы это работало, в строке соединения с базой должно быть прописано: allow user variables=true;

Запрос, выводящий номера строк выглядит вот так:

SELECT @row:=@row+1 row_number, t.* 
FROM tbl t
INNER JOIN (SELECT @row := 0) c

T-SQL: Функция ROW_NUMBER

Недавно открыл для себя очень удобную в некоторых случаях функцию ROW_NUMBER. Появилась она в SQL Server 2005.

Ее можно использовать в селектах для нумирации строк, можно с помощью нее селектить только строки с такого-то по такой-то номер, а так же можно выдавать номер строки в некоторй группе строк. Примеры можно посмотреть в описании.

Мне больше всего нравиться последний случай ее применения. Т.к. раньше такого рода задачи приходилось решать через курсоры, что вело к большим блокам кода и тормозам при исполнении.

Т.е. допустим есть таблица:

CREATE TABLE user_items
(
    id int not null primary key,
    user_id int not null,
    item_id int not null,
    added_at datetime not null
)

И хочется, что бы для каждого пользователя в таблице осталось не более 5 записей, которые были добавлены последними. С использованием ROW_NUMBER это можно сдедать так:

delete u from user_items u
inner join
(
    select ROW_NUMBER() OVER (partition by user_id order by added_at desc) rn, user_id, item_id, id
    from user_items
) a on u.id = a.id
where a.rn > 5

 Т.е. во вложенном запросе выбираются все записи и проставляются номера записей для каждого пользователя в обратном порядке добавления. Соответственно записи, у которых номер больше 5, надо удалить.